Improve performance of like queries.

Database
Enthusiast

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||'%';

Tags (3)
5 REPLIES
Junior Contributor

Re: Improve performance of like queries.

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

Dieter

Enthusiast

Re: Improve performance of like queries.

Dieter,

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

Junior Contributor

Re: Improve performance of like queries.

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

Enthusiast

Re: Improve performance of like queries.

Thanks Dieter -:)

Re: Improve performance of like queries.

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!