On 1/28/19 8:10 AM, Durgamahesh Manne wrote:
On Mon, Jan 28, 2019 at 6:34 PM Ron <ronljohnso...@gmail.com <mailto:ronljohnso...@gmail.com>> 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)) The query optimizer probably can't use indexes on those tables.
Hi why query optimizer can't use indexes on those columns of the tables
Because of the ltrim() functions. B-trees sort the data on the field values, and ltrim() changes that. For example, these two strings are different, and therefore the b-tree puts them in different places in the tree.
snagglefrob snagglefrob Using lrtrim() makes them logically eqivalent, but not physically equivalent. -- Angular momentum makes the world go 'round.
