?
Solved

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

Posted on 2007-10-19
13
Medium Priority
?
183 Views
Last Modified: 2010-03-19
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?
0
Comment
Question by:skacore
  • 6
  • 5
  • 2
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20110833
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?

0
 
LVL 1

Author Comment

by:skacore
ID: 20111194
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.
0
 
LVL 7

Expert Comment

by:assyst
ID: 20111566
Hmm.. that's interesting. Any change when running the script provided by AngelIII or still taking the same time.?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:skacore
ID: 20111623
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.
0
 
LVL 7

Expert Comment

by:assyst
ID: 20111689
Check the result after re_index builds. Also update statistics on the table could help but not sure.

 
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20112084
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
0
 
LVL 1

Author Comment

by:skacore
ID: 20112210
what's the best way to check if stats are up to date? we update them nightly.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20112261
>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...
0
 
LVL 1

Author Comment

by:skacore
ID: 20113769
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?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20114053
>I was under the impression that stats updated automatically
they do, but eventually not "fast" enough.
so, this could be the issue. try to update the stats just before you run the queries, and see if that helps
0
 
LVL 1

Author Comment

by:skacore
ID: 20114980
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20115059
can be done online, it will only read from the table to create the stats.
0
 
LVL 1

Author Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question