Optimizing LIKE OR Selects

I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example:

table Company, fields CompanyID, CompanyName

table Address, fields AddressID, AddressName

table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID

CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter:

Select c.CompanyID, c.CompanyName, a.AddressName

FROM Company c

LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID

LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID

WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%))

There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address.

Is there any way to optimize this query, or is it a problem with the base table implementation?

Any advice would be appreciated.

? Hi Brian, Do you really need to use OUTER JOINs here? For an INNER JOIN, the optimizer can sometimes pick a more efficient execution plan. It's a long shot, but sometimes performance for a query with OR can be improved by re-writing it as a UNION of two queries: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION -- Or UNION ALL, see below SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' If your data is such that you can be sure there will never be an overlap in the results of the two UNION'ed queries, then change UNION to UNION ALL to gain some more performance. If that's not possible, then you could also try how this one runs: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION ALL SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' AND c.CompanyName NOT LIKE 'TEST%' -- Assumes CompanyName is never NULL The queries above are untested. See www.aspfaq.com/5006 if you prefer a tested reply. -- Hugo Kornelis, SQL Server MVP <Brian S. Ward@.discussions.microsoft..com> schreef in bericht news:822eb526-281c-409e-80df-9a03e79d3f05@.discussions.microsoft.com... I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example: table Company, fields CompanyID, CompanyName table Address, fields AddressID, AddressName table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter: Select c.CompanyID, c.CompanyName, a.AddressName FROM Company c LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%)) There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address. Is there any way to optimize this query, or is it a problem with the base table implementation? Any advice would be appreciated.|||

Hi Hugo,

Thanks for replying to my question. I tried a couple of the things that you mentioned.

Changing the OUTER JOINS to INNER JOINS had no noticeable effect on performance. Additionally, the execution plan seemed to become more complicated.

I tried using a UNION ALL clause between 2 SQL statements setup specifically to select AddressName and CompanyName, but performance was destroyed trying that. I used the actual version of the SQL rather than the test SQL I submitted, which contains about 7 joins.

The only solution that I can think of at this time is to change the schema of the base tables, moving the AddressName and CompanyName into the associative table, therefore allowing one search field to be indexed. It would be a bit more cryptic, but would solve the problem of the LIKE OR issue ( since there would be only one LIKE statement for both checks ).

Any other ideas would be appreciated.

|||

It sounds like having a denormalized schema like you suggest may speed things up. There is nothing wrong in duplicating the addressname and company name fields in the one table, lots of companies have denormalized databases for performance purposes. I used to work on a database for one of the biggest Oil companies in the world, and that was largely denormalized and had no relationships set up (they were enforced by triggers and in the stored procedures).

An alternative which may work (although its a long shot) is to rewrite the OR as a not and such that

A OR B = NOT(NOT A AND NOT B)

One of my former colleagues used to assure me that was faster, but I have never tested it. It works as all computers are built from NAND gates, and thus any boolean statement can be rewitten as a series of NANDs

|||

Hi,

Have you tried to run it as to queries?

Without the OR statement.

Try that and see if it gets better.

If so, then insert the result into a temptable and make the final select from there.

It's hard to speed upp OR selects.

Regards

|||

I've tried that too, running 2 queries then trying to merge them after, but that becomes pretty convoluted trying to decide which records from the 2 sets makes the Top 100. I've decided to go with the denormalization plan for now, populating a 'Name' field in the associative table and using that to search. One index, no Or statement, runs really fast.

Thanks to everyone for your input.

|||

Can you post the statistics profile output (and the xml showplan, if possible)?

We can tell where things are wrong based on that.

Thanks,

Conor

sql

Optimizing LIKE OR Selects

I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example:

table Company, fields CompanyID, CompanyName

table Address, fields AddressID, AddressName

table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID

CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter:

Select c.CompanyID, c.CompanyName, a.AddressName

FROM Company c

LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID

LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID

WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%))

There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address.

Is there any way to optimize this query, or is it a problem with the base table implementation?

Any advice would be appreciated.

? Hi Brian, Do you really need to use OUTER JOINs here? For an INNER JOIN, the optimizer can sometimes pick a more efficient execution plan. It's a long shot, but sometimes performance for a query with OR can be improved by re-writing it as a UNION of two queries: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION -- Or UNION ALL, see below SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' If your data is such that you can be sure there will never be an overlap in the results of the two UNION'ed queries, then change UNION to UNION ALL to gain some more performance. If that's not possible, then you could also try how this one runs: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION ALL SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' AND c.CompanyName NOT LIKE 'TEST%' -- Assumes CompanyName is never NULL The queries above are untested. See www.aspfaq.com/5006 if you prefer a tested reply. -- Hugo Kornelis, SQL Server MVP <Brian S. Ward@.discussions.microsoft..com> schreef in bericht news:822eb526-281c-409e-80df-9a03e79d3f05@.discussions.microsoft.com... I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example: table Company, fields CompanyID, CompanyName table Address, fields AddressID, AddressName table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter: Select c.CompanyID, c.CompanyName, a.AddressName FROM Company c LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%)) There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address. Is there any way to optimize this query, or is it a problem with the base table implementation? Any advice would be appreciated.|||

Hi Hugo,

Thanks for replying to my question. I tried a couple of the things that you mentioned.

Changing the OUTER JOINS to INNER JOINS had no noticeable effect on performance. Additionally, the execution plan seemed to become more complicated.

I tried using a UNION ALL clause between 2 SQL statements setup specifically to select AddressName and CompanyName, but performance was destroyed trying that. I used the actual version of the SQL rather than the test SQL I submitted, which contains about 7 joins.

The only solution that I can think of at this time is to change the schema of the base tables, moving the AddressName and CompanyName into the associative table, therefore allowing one search field to be indexed. It would be a bit more cryptic, but would solve the problem of the LIKE OR issue ( since there would be only one LIKE statement for both checks ).

Any other ideas would be appreciated.

|||

It sounds like having a denormalized schema like you suggest may speed things up. There is nothing wrong in duplicating the addressname and company name fields in the one table, lots of companies have denormalized databases for performance purposes. I used to work on a database for one of the biggest Oil companies in the world, and that was largely denormalized and had no relationships set up (they were enforced by triggers and in the stored procedures).

An alternative which may work (although its a long shot) is to rewrite the OR as a not and such that

A OR B = NOT(NOT A AND NOT B)

One of my former colleagues used to assure me that was faster, but I have never tested it. It works as all computers are built from NAND gates, and thus any boolean statement can be rewitten as a series of NANDs

|||

Hi,

Have you tried to run it as to queries?

Without the OR statement.

Try that and see if it gets better.

If so, then insert the result into a temptable and make the final select from there.

It's hard to speed upp OR selects.

Regards

|||

I've tried that too, running 2 queries then trying to merge them after, but that becomes pretty convoluted trying to decide which records from the 2 sets makes the Top 100. I've decided to go with the denormalization plan for now, populating a 'Name' field in the associative table and using that to search. One index, no Or statement, runs really fast.

Thanks to everyone for your input.

|||

Can you post the statistics profile output (and the xml showplan, if possible)?

We can tell where things are wrong based on that.

Thanks,

Conor

Optimizing an IN clause

I have a fairly straightforward SELECT query that includes the following:
MembersTable.MemberID IN
(
SELECT ZipcodesTable.MemberID
FROM ZipcodesTable.Zipcode IN
(
'01234','03631','55902' ... '03036'
)
That is, it's looking for entries in the ZipcodeTable where the Zipcode
value is any one of a very large set of zipcodes, up to 500 Zipcodes. My
Zipcode field is a 5 character field.
Would the query run faster if I made the Zipcode field an int instead of 5
chars?
Are there other ways to speed up the zipcode-matching part of my query?
- Roger GarrettChanging to integer is not possible in this case, cause you got leading
zero in your zip code. Converting them to INT would trim them away. The
best way to speed this up would be top store the ZIP codes in a table
to join them. It would be even more easy to manage.
HTH, Jens Suessmeyer.|||Relations. The performance of this query would benefit from using a join
instead of using the IN operator.
How do the values get into the query?
ML
http://milambda.blogspot.com/|||Have you tried placing the zipcodes in a table and using EXISTS instead of
IN?
Andrew J. Kelly SQL MVP
"Roger Garrett" <RogerGarrett@.discussions.microsoft.com> wrote in message
news:5C55A707-6E72-4CDF-BFC0-7320AA8CDA12@.microsoft.com...
>I have a fairly straightforward SELECT query that includes the following:
> MembersTable.MemberID IN
> (
> SELECT ZipcodesTable.MemberID
> FROM ZipcodesTable.Zipcode IN
> (
> '01234','03631','55902' ... '03036'
> )
> That is, it's looking for entries in the ZipcodeTable where the Zipcode
> value is any one of a very large set of zipcodes, up to 500 Zipcodes. My
> Zipcode field is a 5 character field.
> Would the query run faster if I made the Zipcode field an int instead of 5
> chars?
> Are there other ways to speed up the zipcode-matching part of my query?
> - Roger Garrett
>|||>> Would the query run faster if I made the Zipcode field [sic] an INTEGER instea
d of CHAR(5)? <<
ZIP codes are CHAR(5) and not INTEGER. Columns are not fields.
For a large number of zip codes, you might find using a table instead
of a list is faster. It would have an index on its single column.
SELECT member_id
FROM Membership
WHERE zip_code
IN (SELECT zip_code FROM ZipLists);|||Hi Roger,
Make sure you have an index ZipCode, MemberID on the ZipcodesTable.
Also, use EXISTS instead of IN.
AND EXISTS (
SELECT *
FROM ZipcodesTable zt
WHERE zt.Zipcode IN ( ...... )
AND zt.MemberID = MembersTable.MemberID
)
Even better if you could put the IN clause into a table of its own...
AND EXISTS (
SELECT *
FROM #ZCodes zt
WHERE zt.MemberID = MembersTable.MemberID
)
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Roger Garrett" <RogerGarrett@.discussions.microsoft.com> wrote in message
news:5C55A707-6E72-4CDF-BFC0-7320AA8CDA12@.microsoft.com...
>I have a fairly straightforward SELECT query that includes the following:
> MembersTable.MemberID IN
> (
> SELECT ZipcodesTable.MemberID
> FROM ZipcodesTable.Zipcode IN
> (
> '01234','03631','55902' ... '03036'
> )
> That is, it's looking for entries in the ZipcodeTable where the Zipcode
> value is any one of a very large set of zipcodes, up to 500 Zipcodes. My
> Zipcode field is a 5 character field.
> Would the query run faster if I made the Zipcode field an int instead of 5
> chars?
> Are there other ways to speed up the zipcode-matching part of my query?
> - Roger Garrett
>|||Jens,
I don't see that leading zeros would cause any problem. I'm suggesting that
I change the Zipcodes column from char 5 to int and to just store the
numerical integer values of the zipcodes rather than the 5-character string.
A zipcode of "03036" would become a 3036 in the column, and when I'm
searching for an "03036" it would match with the 3036 value. Of course, I
would specify numeric values (e.g. 03036) rathet than quoted strings
('03036').
I'm assuming that a 5-char column occupies at least 6 bytes (to make it on
an even byte boundary) and that an int occupies 4 bytes. That at a minium
saves some space in the database. And then, when I'm looking for a particula
r
value, it only has to compare 4 bytes (which is mostly likely a single
hardware instruction) instead of six bytes, so the queries should run a bit
faster.|||ML,
My application program constructs the query.
What's happenning here is this: I have a Zipcodes table. That table has two
columns, a MemberID column and a Zipcode column. Each member has one entry i
n
this table, signifying the zipcode of where he lives.
At certain times during the running of my application it needs to know the
MemebrIDs of all the members that live within a certain radius of a given
member. My application program figures out which zipcodes are within that
radius and constructs an array of strings signifying that set of zipcodes. I
t
then constructs a query, using that array of strings, in order to get from
the database the set of MemberIDs, from the Zipcodes table, of those members
who reside in any of those zipcodes. The query looks something like:
SELECT ZipcodesTable.MemberID
FROM ZipcodesTable.Zipcode IN
(
'01234','03631','55902' ... lots of zipcodes here ... '03036'
)
As far as I can tell that means that SQL Server has to compare each and
every zipocde in the Zipcodes table with (possibly all of) the zipcodes
within the IN clause of the query. In fact, for MOST of the rows in the
Zipcodes table it will have to do the comparison against ALL of the zipcodes
in the IN clause, since most of the members will not be within ANY of those
zipcodes.
Now, if SQL Server were smart it might order those zipcodes from the IN
clause and determine the smallest and largest zipcode values and thereby do
a
much quicker comparison at each row. But I don't know that I can rely on SQL
Server being that smart.
So I'm looking for a better way to express the query so that it runs as fast
as possible.|||Andrew,
I wasn't familiar with the EXISTS operator (I'm very new to all this) so I
just now read up on it. I don't see how EXISTS will help. How are you
suggesting that the zipcodes be put in a table? Do you mean the set of
zipcodes that I'm looking for for the specific current query? WHat would the
EXISTS query look like?
Please see my reply to Jens for a (hopefully) clearer description of what
I'm trying to accomplish.
"Andrew J. Kelly" wrote:

> Have you tried placing the zipcodes in a table and using EXISTS instead of
> IN?
> --
> Andrew J. Kelly SQL MVP
>
> "Roger Garrett" <RogerGarrett@.discussions.microsoft.com> wrote in message
> news:5C55A707-6E72-4CDF-BFC0-7320AA8CDA12@.microsoft.com...
>
>|||For the performace reason: Try it out, once you converted this into
have your data as a char. (which is like I said and meanwhile also
Steve pointed out, preferable, because you don=B4t have to deal with
later problems around this. We has ourselves in Germany a change from
4digit numbers to 5 digits with a trailing zero. I can tell you, that
was for many software vendors like the Y2k problem).
These is my opinion, my personal experience and advice for you.
HTH, Jens Suessmeyer.

optimize nologging

Hi,
We have a reporting database with simple recovery model.
To improve performance we have to use SELECT..INTO clause
and create all tables...but problem now is that each
table is populating from 3-4 different result set...so if
we use SELECT ..INTO for first load(we can't use UNION in
SELECT..INTO CLAUSE) then for next 3-4 loads we have to
use INSERT INTO SELECT clause that will do lot of logging.
What are the possible options that we can use in this
scenario?
For temporary solution we are thinking of using SELECT
INTO and create 4 temp tables then bcp out the data and
then use BULK INSERT into origional table --what can be
possible flaws in this scenario?
Thanks
--HarvinderYou can use a derived table in the select statement of the select into, for
example:
SELECT column_1, column_2 INTO new_table
FROM
(SELECT column_1, column_2 FROM table_1
UNION ALL
SELECT column_1, column_2 FROM table_2) AS old_table
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"harvinder" <hs@.metratech.com> wrote in message
news:072401c3787a$cf8048a0$a001280a@.phx.gbl...
> Hi,
> We have a reporting database with simple recovery model.
> To improve performance we have to use SELECT..INTO clause
> and create all tables...but problem now is that each
> table is populating from 3-4 different result set...so if
> we use SELECT ..INTO for first load(we can't use UNION in
> SELECT..INTO CLAUSE) then for next 3-4 loads we have to
> use INSERT INTO SELECT clause that will do lot of logging.
> What are the possible options that we can use in this
> scenario?
> For temporary solution we are thinking of using SELECT
> INTO and create 4 temp tables then bcp out the data and
> then use BULK INSERT into origional table --what can be
> possible flaws in this scenario?
> Thanks
> --Harvinder
>

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.