3 Replies Latest reply on Sep 12, 2013 2:02 PM by tanmoypalit

    Performance issues with string concatenation

    tanmoypalit Newbie

      Hi,

      Following simple concatenation query is taking almost 2 mins to return only 27 records.

      SELECT * FROM RUN_OBSERVATION WHERE convert(CONCAT (Activity_ID, Run_ID), string)='10021'

       

      on the other hand if I remove the CONCAT and execute the query, then it runs in less than a second.

      SELECT * FROM RUN_OBSERVATION WHERE Activity_ID='1002'

       

      Also tried "||" operator but the result is same.

       

      We have a requirement to concatenate the columns and then apply search but we running into these serious performance issues.

       

      Will appreciate any help with this.

       

      Regards

      Tanmoy Palit

        • 1. Re: Performance issues with string concatenation
          Steven Hawkins Master

          > on the other hand if I remove the CONCAT and execute the query, then it runs in less than a second.

           

          A couple of things to check - is the criteria being pushed down in the fast query?  Is there a source index/primary key on the activity_id that is speeding up the source query?

           

          That is likely the case.  With that in mind you'll want to compare the two query plans.  If the concat criteria is not being pushed at all, then that is your initial issue.  If it is being pushed, then likely you'll need a function based index at the source level to speed up the query.

           

          Steve

          1 of 1 people found this helpful
          • 2. Re: Performance issues with string concatenation
            Ramesh Reddy Master

            Tanmoy,

             

            Which database? Sounds like the concat function is not being pushed to the source and being evaluated at Teiid engine. If this is custom translator, you need to implement the code in translator such that it can push this query down to the query.

             

            Ramesh..

            1 of 1 people found this helpful
            • 3. Re: Performance issues with string concatenation
              tanmoypalit Newbie

              Thanks Ramesh/Steven.

               

              Actually both of you are right.

              First I am using a custom translator which did not have the CONCAT implemented - I have added this. Now the query runs little faster than previous.

              But I think as Steven said, when we put the CONCAT in WHERE, it no longer had the indexes available. I am looking into this now.

               

              -

              Tanmoy