Performance issues with string concatenation

[tanmoypalit]

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

[shawkins]

> 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

[rareddy]

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..

[tanmoypalit]

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