June 12, 2008 at 12:40 pm
I have a stored procedure that when run in one query window runs in 3 seconds and when run in another runs in 1 second. This happens consistently, without fail. Using exactly the same parameters and the same session settings. I've checked the plan cache and exactly the same execution plan is being reused each time.
I ran a trace and found the part of the stored procedure that runs more slowly is this part:
WITH tblname AS
(
SELECT CONVERT(datetime, dateadd(day, -500, @var1)) field1
UNION ALL
SELECT field1 + 1
FROM tblname
WHERE field1 + 1 <= dateadd(day, 10, @var1)
)
SELECT field1 INTO #tbl FROM tblname OPTION (MAXRECURSION 0)
#tbl is dropped later in the stored procedure.
Why or how could this happen?
SQL 2005 Standard SP2 x64.
June 12, 2008 at 1:05 pm
It appears that you are using a recursive CTE to do something that could be easily done with a Tally table.
Here is an article that will help you with this: http://qa.sqlservercentral.com/articles/TSQL/62867/
😎
June 12, 2008 at 1:19 pm
Are you seeing two different execution plans too?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 13, 2008 at 1:14 am
No, as I mentioned in my first post, the same plan is being reused. That's why I can't understand the difference in the execution time.
I'll look into the tally table but it still doesn't explain why the same query would consistently execute in different times.
Thanks
June 13, 2008 at 6:16 am
When you ran the trace did you also see two different times on the server? I understand the client saw two times, but was it the same on the server?
Oh, wait... Are you getting recompiles during execution? That might explain it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 13, 2008 at 10:34 am
The execution time is different on the server, which I saw in the trace. There were no recompiles.
June 13, 2008 at 10:58 am
I'm flummoxed.
When I said recompiles, I did mean recompiles caused by the code, not a recompile that you ran. Do you have anying in the code that would cause the query to recompile during execution?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 13, 2008 at 12:36 pm
I've replaced the CTE with a set-based method instead as suggested earlier. That has sped up the stored procedure a bit so thanks for that suggestion. It was a good article.
However, I still have the same problem with the new procedure whereby it runs much quicker in one query window than it does in another. I've attached a screenshot of the trace that shows the same stored procedure running in 339ms for SPID 64 and 114ms for SPID 92. No matter how many times I run it SPID 92 is always at least as twice as quick.
Any help will be very much appreciated.
June 13, 2008 at 3:51 pm
What happens if you make new sessions?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply