question

ash avatar image
ash asked

like operate giving timeout while doing wild search for 'ABCZ%'

Hello all

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

environment:

VB6.0

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

Example:

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'


Problem:

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

Solutions tried:

  - 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

regards ash

sql-server-2000querylikevbtimeout-expired
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered

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!

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

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?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - REVERSE is under used in my opinion :)
2 Likes 2 ·
Grant Fritchey avatar image
Grant Fritchey answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 Without looking at the actual plan it's using (not to be confused with the estimated plan) you're just guessing at why it's slow. The plans will tell you why the queries differ in performance.
1 Like 1 ·
ash avatar image
ash answered

Hello sincere apologies to all as i did not try any of the suggestion mentioned. Simple SQL Server SP4 update did the trick - problem is solved for now. bye for now will probably come again with such silly question soon thanks!!

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

There are no silly questions, just silly answers.
0 Likes 0 ·
can you mark the question as answered - even by your own answer so its updated properly please?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.