On 1/28/19 5:04 AM, Ron wrote: > On 1/28/19 6:20 AM, Durgamahesh Manne wrote: >> Hi >> >> below query is being executed for long time >> >> Select >> distinct ltrim(rtrim(ssnumber)), CL.iInsightClientId, >> ltrim(rtrim(TFA.client_account_key)) as vchAccountNumber, >> concat(TFA.first_name, ' ', TFA.last_name) as vchAccountName, >> concat('AP:TIAA', TFA.routing_number) as vchRepLabel, >> null as vchOpenDate, TFA.address1 as vchAccountTiteLine2, >> TFA.address2 as vchAccountTitleLine3,null as vchAccountType, >> TFA.address1 as vchPrimaryContact, 'TIAA-CREF' as Custodian, >> TFA.routing_number as vchCustodian_RepId, null as vchCustodianRepName, >> CONCAT ('TIAA-CREF:',ltrim(rtrim(client_account_key)),':', >> ltrim(rtrim(ssnumber))) as vchAccountKey, >> null as vchFeedsAccountType >> from accounts as TFA >> join client3 as CL on >> ltrim(rtrim(Cl.vchssnumber))=ltrim(rtrim(TFA.ssnumber)) >> left join account3 as AC on >> ltrim(rtrim(AC."vchCustodianAccountNumber"))=ltrim(rtrim(TFA.client_account_key)) >> >> where AC."iInsightAccountID" is null; >> >> query is being executed for long time even after i have created >> required indexes on columns of the tables >> please help for fast query execution > > I bet this is what's causing your troubles: > on ltrim(rtrim(Cl.vchssnumber))=ltrim(rtrim(TFA.ssnumber))
There is also the question of why not do?:
btrim(Cl.vchssnumber) = btrim(TFA.ssnumber)
> > The query optimizer probably can't use indexes on those tables. > >
