All Forums Database
taghe 1 post Joined 12/13
20 Apr 2015
optimisation of a query with TRIM function

hello everyone.

 

i have an INSERT query with a subquery like this one:

 

SELECT 1

 

            FROM SD_CRM.D_INDIV_PREF DIP

 

            WHERE DIP.SITE = FPL.SITE

 

                AND  TRIM(DIP.ID_CLIENT) = TRIM(FPL.ID_CLIENT)

 

                AND  FPL.PREF_KEY = DIP.PREF_KEY

 

                AND  FPL.PREF_KEY_N = DIP.PREF_KEY_N

 

 

 

the step for the all retrieval, hash distribution and sort from the table SD_CRM.D_INDIV_PREF take more than 2h with 200million rows.

 

But when i remove the TRIM function. it take really less time around 1min.

 

LOVE
dnoeth 4628 posts Joined 11/04
21 Apr 2015

Do you really need that TRIM? 
Do you actually have leading blanks in those columns? Ouch, that's worst case, you should clean your data immediatly and change your loading process.
If there are only trailing blanks you don't need the TRIM at all, but you should still clean your data.

Dieter

You must sign in to leave a comment.