optimisation of a query with TRIM function

Database
Teradata Employee

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.

1 REPLY
Senior Apprentice

Re: optimisation of a query with TRIM function

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.