How do I increase efficiency of a SQL SELECT using the LIKE operator in a WHERE clause?

book

Article ID: 15608

calendar_today

Updated On:

Products

CA Datacom - DB CA Datacom CA Datacom - AD CA Datacom - Server CA CIS CA Common Services for z/OS CA 90s Services CA Database Management Solutions for DB2 for z/OS CA Common Product Services Component CA Common Services CA Datacom/AD CA ecoMeter Server Component FOC CA Easytrieve Report Generator for Common Services CA Infocai Maintenance CA IPC Unicenter CA-JCLCheck Common Component CA Mainframe VM Product Manager CA Chorus Software Manager CA On Demand Portal CA Service Desk Manager - Unified Self Service CA PAM Client for Linux for zSeries CA Mainframe Connector for Linux on System z CA Graphical Management Interface CA Web Administrator for Top Secret CA CA- Xpertware

Issue/Introduction



I have a program accessing Datacom tables using the SQL command below but it is taking a long time to run.

SELECT ID, NAME, CODE 
FROM VENDOR
WHERE  NAME LIKE '%PROCESS%%%%%%%%%%%%%%';

How do I increase efficiency of this SQL SELECT using the LIKE operator?

Will creating a VIEW help? 

Environment

Release: DATABB00200-14-Datacom/AD
Component:

Resolution

Creating a view will not improve performance of the SQL SELECT.

The way to improve performance is to add a KEY for the column referred to in the in LIKE operator in the WHERE clause. In the example above, add a KEY on the NAME column.