Optimising Select statements which has a LIKE where clause.

Hi all

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 dont 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:

strAddressLine1, strAddressLine2, strAddressLine3, strAddressLine4, strPostCode.

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).

Any advice truly appreciated.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.|||Like is one of those "fuzzy" things and does not use indexes. Postcodes are notoriously difficult to search on...

In your example you give "W1%"

realise that this will return W12 etc

I in the time I had to do this tried to Narrow the user down to the local as in W1
W12 etc

Alternative prospect here

Split your Postcode into two fields (I know it sounds wierd) but then you can do an = rather than a like and an index can be used!|||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.

-PatP|||I get an index seek with a bookmark lookup

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(strPostCode varchar(10), Col2 int)
GO

INSERT INTO myTable99(strPostCode, Col2)
SELECT 'W1Me',1 UNION ALL
SELECT 'W2Me',2 UNION ALL
SELECT 'W3Me',3 UNION ALL
SELECT 'W4Me',4
GO

CREATE INDEX myTable99_strPostCode ON myTable99(strPostCode)
GO

--[CTRL]+K
Select Top 3000 * from myTable99 with(nolock) Where strPostCode LIKE 'W1%'
GO

SET NOCOUNT OFF
GO|||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...

-PatP|||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 dont 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 (Im 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.

Regards

Eamon.|||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:
EXEC sp_updatestats
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|||mwolf! Your a Star!!!

Select Top 3000 * from TL_ClientAddresses with(nolock, INDEX (your_index_name)) Where strPostCode LIKE W1%

Works a treat!!! Gone from over a minute down to 6 seconds just by adding the 'INDEX()' statement.

I think that's the answer to my problem, Thankyou very much!!!

Regards

Eamon.