Tutorial :How do I improve SQL Server query performance when doing a LIKE over many columns



Question:

My query needs to do a LIKE search over 3 columns.

SELECT * FROM Monkeys  WHERE [Name] LIKE '%pete%' OR [Desc] LIKE '%pete%' OR [Info] LIKE '%pete%';  

I am looking to improve the performance of this query.

I can't use full-text catalogs, just simple tables. There are about 200,000 rows (SQL Server 2008 database) and it takes 3 to 6 seconds.

Doe anyone have any tips?


Solution:1

Full text indexing/search if you have leading wildcards.

No amount of normal indexing will help you to make it fast, sorry, whether multiple or single columns with wildcards.

Your best least worst chance is to have composite index on the 3 columns and hope the optimizer scans this rather a table scan, but frankly I've not tried it myself. And likely you can't because index key column combined width has to be less than 900 bytes


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »