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

Question

question

ash asked Mar 10, '10

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-2000 query like timeout-expired vb

10 |1200

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

Answer 1 · 2010-03-10T08:44:46Z

David Wimbush answered Mar 10, '10

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

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

Answer 2 · 2010-03-10T08:58:35Z

Fatherjack answered Mar 10, '10

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

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

Answer 3 · 2010-03-10T08:59:51Z

Rob Farley answered Mar 10, '10

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

10 |1200

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

Fatherjack ♦♦ commented · Mar 10, 2010 at 09:13 AM

+1 - REVERSE is under used in my opinion :)

2 ·

Answer 4 · 2010-03-10T10:13:33Z

Grant Fritchey answered Mar 10, '10

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

10 |1200

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

David Wimbush commented · Mar 10, 2010 at 11:08 AM

+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 ·

Answer 5 · 2010-03-11T11:05:40Z

ash answered Mar 11, '10

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

10 |1200

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

David Wimbush commented · Mar 11, 2010 at 12:15 PM

There are no silly questions, just silly answers.

0 ·

Fatherjack ♦♦ commented · Mar 11, 2010 at 01:48 PM

can you mark the question as answered - even by your own answer so its updated properly please?

0 ·

question