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.
Author |
Topic |
yoyosh
Starting Member
27 Posts |
Posted - 2013-01-29 : 02:12:32
|
I created simple query with several joins and a top(5) clause, which took more than a minute to execute.However when I changed:select top(5)to declare @i int set @i = 5 select top (@i) ...It took less than a second to execute. Why is that?Below are links to execution plans:top(5): http://imageshack.us/f/24/topsim.png/top(@i): http://imageshack.us/f/203/topvar.png/Thank you for help in advance |
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2013-01-29 : 07:12:53
|
Perhaps the execution plan was cached after the first run, and will make any following run a lot faster? Try running it the first way again and see.How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
yoyosh
Starting Member
27 Posts |
Posted - 2013-01-29 : 08:06:36
|
I tried. It does not affect execution times in both cases. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-29 : 08:11:18
|
Enable execution plan (control-m) and run both in the same query window. It will show you the relative costs and query plans. Examine the different query plans to see what is different. Is it just the query that you have shown that you are running, or is there more to it? Is it part of a stored procedure? If it is part of a stored procedure (or even if it is not), you can try statement level recompilation hint (add OPTION (RECOMPILE) at the end of the statement) to see if that makes any difference. |
|
|
yoyosh
Starting Member
27 Posts |
Posted - 2013-01-29 : 08:14:44
|
As you could see from my initial post, i enabled them from the beginning. I have even attached links to them. What can you say about them? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-29 : 08:25:41
|
Ah, I didn't see that - I am not able to access images from behind the corporate firewall. Hopefully someone else who is able to see the images can comment. |
|
|
yoyosh
Starting Member
27 Posts |
Posted - 2013-01-30 : 07:37:56
|
Anyone could help me on that? |
|
|
|
|
|