Unanswered: Optimising Select statements which has a ‘LIKE’ where clause.
I have been doing some development work in a large VB6 application. I have updated the search capabilities of the application to allow the user to search on partial addresses as the existing search routine only allowed you to search on the whole line of the address.
Simple change to the stored procedure (this is just an example not the real stored proc):
From: Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode = ‘W1 ABC’ To: Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode LIKE ‘W1%’
Now this is when things went a bit crazy. I know the implications of using ‘with(nolock)’. But seeing the code is only using the ID field to get the required row, and the database is a live database with hundreds of users at any one time (some updating), I think a dirty read is ok in this routine, as I don’t want SQL to create a shared lock.
Anyway my problem is this. After the change, the search now created a Shared Lock which sometimes locks out some of the live users updating the system. The Select is also extremely SLOW. It took about 5 minutes to search just over a million records (locking the database during the search, and giving my manager good reason to shout abuse at me). So I checked the indexes. I had an index set on:
So I created an index just for the strPostCode (non clustered).
This had no change to the ‘Like select’ what so ever. So I am now stuck.
1) Is there another way to search for part of a text field in SQL.
2) Does ‘Like’ comparison use the index in any way? If so how do I set this index up?
3) Can I stop a ‘Shared Lock’ being created when I do a ‘like select’?
4) Do you have any good comebacks I could tell the boss after his next outburst of abuse (please not so bad that he sacks me).
1. I have been working on smaller database systems the last couple of years but as for number 1 try changing the query to "=" with a wild card "%" in the QA with the show execution plan on and see if the index is being used.
2. I seem to remember that the like operator cancels the index. To check this I would execute the query in the QA and check the execution plan.
3. Don't know off the top of my head.
4. Tell your boss that software like the people who create it are imperfect things.
I'd try to run the query in the Query Analyzer. In the form that you posted the query, it ought to use the PostCode index. It ought to be able to ride the index as far as the first wildcard (percent sign in this case). If you examine the query plan, it might give you some idea of where the problem is.
SET NOCOUNT ON
CREATE TABLE myTable99(strPostCode varchar(10), Col2 int)
INSERT INTO myTable99(strPostCode, Col2)
SELECT 'W1Me',1 UNION ALL
SELECT 'W2Me',2 UNION ALL
SELECT 'W3Me',3 UNION ALL
CREATE INDEX myTable99_strPostCode ON myTable99(strPostCode)
Select Top 3000 * from myTable99 with(nolock) Where strPostCode LIKE 'W1%'
SET NOCOUNT OFF
Yeah, but that's using the code that they posted, which might or might not generate the same plan as the code they are actually using Not that I've ever been burned by different code being posted than what is actually run before, I just read about it in this book once...
Optimising Select statements which has a ‘LIKE’ where clause: Part2.
First like to thank everyone who replied. Very much appreciated.
Unfortunately I am still not much closer at finding a solution. I have done the execution plan thing although I can see a index seek, I don’t think the index I have created is being used (but I am not sure).
If I delete the index, then the search time on about 500,000 records (I’m testing from a subset of the total number of rows in the table) is about the same as when the index is present. If I do a ‘=’ search, then with an index runs in less than a second and without takes much longer.
Anyway if anyone has any other ideas or a total different approach I can take then please let me know. Also if anyone knows of any websites or books that look into ‘like selects’ more deeply than just giving you the syntax, like every book and site I have found, then that would be good too.
1. Try use index hint:
Select Top 3000 * from TL_ClientAddresses with(nolock, INDEX (your_index_name)) Where strPostCode LIKE ‘W1%’
2. Try update statistics:
3. Try change query:
Select Top 3000 * from TL_ClientAddresses with(nolock)
Where strPostCode >= ‘W1 ’ AND strPostCode =< ‘W1z’
4. If your query fetch more then 20% table then optimizer don't use index