All Forums Database
barani_sachin 141 posts Joined 01/12
13 Jun 2013
Improve performance of like queries.

Hi All,
    Is there anyway to improve the performance(Reducing the CPU consumed is the main aim for now) of queries involving like in the where clause.
 
 
WHERE FIRST_NM LIKE :V_FIRST_NM||'%'
AND LAST_NM LIKE :V_LAST_NM||'%';

dnoeth 4628 posts Joined 11/04
14 Jun 2013

You might try to rewrite this LIKE using POSITION or SUBSTRING, but it will probably not reduce CPU.
 
Dieter

Dieter

barani_sachin 141 posts Joined 01/12
20 Jun 2013

Dieter,
Could you pls elaborate on how we can rewrite the LIKE using POSITION andSUBSTRING?

dnoeth 4628 posts Joined 11/04
25 Jun 2013
WHERE POSITION(V_FIRST_NM IN FIRST_NM) > 0

or

WHERE SUBSTRING(FIRST_NM FROM 1 FOR CHAR_LENGTH(V_FIRST_NM) = V_FIRST_NM

Dieter

Dieter

barani_sachin 141 posts Joined 01/12
26 Jun 2013

Thanks Dieter -:)

godspeeddeepak 3 posts Joined 09/12
08 Jul 2013

Hello Dieter,
I have been following your Posts for long time and have really helped a lot
Question: Performance Tuning of SQLs results in reduction of Execution Time and CPU Utilization. What are different ways to quantify or measure this in terms of dollar amount/currency?
Thanks!

You must sign in to leave a comment.