2 Replies Latest reply: Aug 2, 2013 7:49 AM by BluShadow RSS

    TRIM - Script never ends

    Street

      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

          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

            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.