Substr causing problem-killing query performance

Database
Enthusiast

Substr causing problem-killing query performance

Hi,

I need to use Substr as part of my join condition.However its killing the performance of the query significantly.

Is there any way i can improve performance of the query?

SELECT s.col1

FROM ABC s

INNER JOIN XYZ t
ON substr(col3,10,2) = t.cols1
AND substr(col3,15,2) = t.cols2

Thanks,
Sam
2 REPLIES
Enthusiast

Re: Substr causing problem-killing query performance

You might want to define col3 as value order NUSI for improving the performance.

But, it is a good practice to keep the substring data substr(col3,10,2) and substr(col3,15,2) as separate columns in source table (ABC here). Because you will be doing this substr operation once but you will be accessing these columns multiple time like in where clause here.

For example, you might want to keep Area Code column separately from phone number columns. When you require to access area code, you can directly access this column without substring it every time.

Hope this helps.
Enthusiast

Re: Substr causing problem-killing query performance

LDM's first normal form requires "atomicity" of each field... as stated by Sunil.