Why is this query slow? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Why is this query slow?

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2013-03-25 : 12:38:09

The subquery portion is extremely fast. I would expect that the outer portion would only run on the results of the subquery so it should be very fast. But it takes ages to run. The activity table is huge...


select * from (
select top 10 systemvars from activity order by activityid desc
) as blah
where SystemVars like '%MSIE 7.0%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-25 : 13:20:42

have a look at execution plan and see which is costly step.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2013-03-25 : 14:38:53

100% clustered index scan on activity table. It took 7 minutes just to get the estimated plan.

I don't understand the order of operations. The sub query is lightening fast and results in 10 records. The outer query should only execute on those 10 records and should also be extremely fast.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2013-03-25 : 14:55:00

Wait a minute!

Now that I've run the "estimated query plan" the query itself is extremely fast. Could it have been the creation of the plan itself that was so slow and now that it has a plan it is very fast? I didn't realize plan creation could be so slow.

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-03-25 : 17:53:20

quote:
Originally posted by ferrethouse

Wait a minute!

Now that I've run the "estimated query plan" the query itself is extremely fast. Could it have been the creation of the plan itself that was so slow and now that it has a plan it is very fast? I didn't realize plan creation could be so slow.

Its not slow, so thats probably not your problem.
Do you have an index on your table? Is "activityid" the first colomn indexed?

Also trying using DBCC DROPCLEANBUFFERS to clear the cache and get a better idea of how long it will run.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources