i am not sure what is going wrong here. i might be missing some basics, going bonkers. need some desperate help to find out the reason for strange behavior. It could be service pack issue, but incase its not...
sql server 2000 sp3
database is on d drive on 2003 server with more than 10GB free space
database has 1 table t1, around 18 column/fields all varchar with 1 bigint field which is primary key, more than 28 million records
7 indexes on 7 different fields - one each
[in context to example below] field3 varchar(10) - (not primary key, not foreign key, no default) - nonclustered index with fill factor = 90
connection timeout is set to 100 - using DAO
a) query used in the source - throws timeout / execution cancelled error - select field1, field2 from t1 where field3 like 'ABC 0HZ%'
b) query used in the source - works fine (without Z) - select field1, field2 from t1 where field3 like '0H%'
c) query used in the source - works fine (without %) - select field1, field2 from t1 where field3 like 'ABC XYZ 0HZ' or - select field1, field2 from t1 where field3 = 'ABC XYZ XYZ 0HZ'
query a above throws timeout error while using like operator to do a wild search for input data ending with Z
queries b and c works fine
- Sql server restarted - rebuild index - dropped and recreated - database detached and attached again - database shrinked after dropping indexes
Hope the point clear - Any help would be highly appreciated
Thanking you in advance
Answer by Rob Farley ·
Searching for data ending in Z will require a scan, and therefore it'll be slow. If this is a common request, perhaps index a computed column of reverse(field3), and then look for where this column starts with Z.
Answer by Grant Fritchey ·
Have you checked the execution plans for these queries? Compare the execution plan for the query that runs well to the one that doesn't. If they're the same, try flushing that query out of the cache and running it with the "bad" parameter to see if you get a different query. Understanding if you're getting a scan or a lookup and where those operations are occurring will help you understand what's going wrong with the query.
Answer by Fatherjack ·
A few things to review -
You should try one index covering all the columns in your query - the optimzer will only select one index to help the query run.
Have you tried the query as a stored procedure.
Are the statistics up to date on the table.
Does the query complete in SSMS, if so how long does it take
How selective is the filter on column3, are there 28M values or only a few hundred?
Answer by David Wimbush ·
I can't remember my DAO that well but I seem to remember that ConnectionTimeout is how long to wait before giving up when trying to open the connection. It's not how long to allow for query execution. Isn't there another property for that? And check what units it's in too - it could be milliseconds or something!