This discussion is archived
2 Replies Latest reply: Aug 2, 2013 5:49 AM by BluShadow RSS

TRIM - Script never ends

Street Newbie
Currently Being Moderated

Hello

 

I have a little sql script and I use trim. But with trim, the script never ends:

 

SELECT     TRIM(c.idcll)as ID,u.udfvals

FROM  CDSRDL c

   INNERJOIN

            RCDSUDF u

                 ON trim(c.udfkeyl)    =trim(u.udfkeyl)

                 AND c.mdgesi     =  0

                 AND   c.cdpermi   <> 3

                 ANDtrim(u.idudfs)     ='AKT'

                 AND u.udfvals    NOTIN('001')

 

Without trim, the script runs only 0.25sec.

 

SELECT     TRIM(c.idcll)as ID,u.udfvals

FROM  CDSRDL c

   INNERJOIN

            RCDSUDF u

                 ON c.udfkeyl    = u.udfkeyl

                 AND c.mdgesi     =  0

                 AND   c.cdpermi   <> 3

                 ANDtrim(u.idudfs)     ='AKT'

                 AND u.udfvals    NOTIN('001')

 

What can I do, that the script needs not so many time?

 

Thanks.

 

Roger

  • 1. Re: TRIM - Script never ends
    chris227 Guru
    Currently Being Moderated

    Probably the indexes on c.udfkeyl or/and u.udfkeyl cant be used any more due to the application of the trim function.

    Workarounds

    1. Update the underlying values with trim

    2. create a functional index on trim (udfkeyl)

  • 2. Re: TRIM - Script never ends
    BluShadow Guru Moderator
    Currently Being Moderated

    By applying a function to your join conditions you will be preventing the database from being able to use indexes, so it's very likely the query will take longer, and if there's a lot of data, that could be a seriously big impact.

     

    Are you sure your joining keys actually need trimming?  That would seem like corrupt data to me to have even generated keys with erroneous additional characters in them.

     

    If it's absolutely necessary, then you'd likely need to create function based indexes on those two.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points