SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Why is this query slow?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ferrethouse
Constraint Violating Yak Guru

324 Posts

Posted - 03/25/2013 :  12:38:09  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 03/25/2013 :  13:20:42  Show Profile  Reply with Quote
have a look at execution plan and see which is costly step.

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

Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

324 Posts

Posted - 03/25/2013 :  14:38:53  Show Profile  Reply with Quote
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.
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

324 Posts

Posted - 03/25/2013 :  14:55:00  Show Profile  Reply with Quote
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.
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 03/25/2013 :  17:53:20  Show Profile  Reply with Quote
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.

Edited by - UnemployedInOz on 03/25/2013 17:56:11
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000