asked on

MSSQL 2k5 - 2 similar queries, one works, one times out.

I have a 2 SQL querys, one runs in a few seconds and the other just keeps running (at 30 minutes now), why?

select * from keywordmatches where
dim_keywordmatch_harvesttime <= '10/19/2007'
and dim_keywordmatch_harvesttime >= '10/18/2007'
and clientid = 3717

This runs in about 5 seconds and i get 400 results

select * from keywordmatches where
dim_keywordmatch_harvesttime <= '10/19/2007'
and dim_keywordmatch_harvesttime >= '10/18/2007'
and clientid = 3353

This runs for over 30 minutes, but I'm very sure there are only about 20 results. The execution plans are both the same, and are very simple. What else can I do to figure this out?

Guy Hengel [angelIII / a3]

what are the data types, and what are the indexes on the tables?

assuming that the data type is datetie on the dim_keywordmatch_harvesttime field:

select *
from keywordmatches
where dim_keywordmatch_harvesttime <= convert(datetime, '10/19/2007', 101)
and dim_keywordmatch_harvesttime >= convert(datetime, '10/18/2007', 101)
and clientid = 3717

and, of course, you need a clustered index on clientid + dim_keywordmatch_harvesttime for best performance.

also: how many records do both queries return, and how many rows are there in the table?

skacore

ASKER

The query with clientid = 3717 returns about 400 rows. The one with 3353 returns about 40 rows. The table is pretty big, over 90 million rows. The clientid is an int, and the other one is a date field. I have just a regular index on each column and not a single index that covers both, although I'm sure that would help.

What is bothering me is how can just changing that one value cause such a difference in how long the query takes? Their execution plans are identical.

assyst

Hmm.. that's interesting. Any change when running the script provided by AngelIII or still taking the same time.?

skacore

ASKER

Same kind of results. The one with 3717 returns in about 10 seconds with about 400 rows. The one with 3353 pretty much times out/takes 20 minutes to return and only has 40 rows. Is it possible that the indexes are just broken? We run the re-index builds on the weekends.

assyst

Check the result after re_index builds. Also update statistics on the table could help but not sure.

Guy Hengel [angelIII / a3]

I agree with the suggestion to check the statistics, and ensure they are up to date.
if the execution plan and timings do differ like that, that's the most probable reason

skacore

ASKER

what's the best way to check if stats are up to date? we update them nightly.

Guy Hengel [angelIII / a3]

>we update them nightly.
then you have reasons for that?
do you have a lot of insert/update/deletes during the day?
you might then consider running another stats update on that table during the day, eventually several times...

skacore

ASKER

we do a LOT of updates/inserts during the day. I was under the impression that stats updated automatically and only needed a manualy update once a day at most. Maybe that is the issue?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

skacore

ASKER

Can update stats be done online or does it lock the table up while it run? We always just do it overnight so I'm not sure.

Guy Hengel [angelIII / a3]

can be done online, it will only read from the table to create the stats.

skacore

ASKER

That seemed to do it. We had our overnight update stats run and now the query is fine. Thanks for all the great knowledge!