Same stored procedure varies in execution time dependent upon query window

  • 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.

  • 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://www.sqlservercentral.com/articles/TSQL/62867/

    😎

  • 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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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

  • 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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • The execution time is different on the server, which I saw in the trace. There were no recompiles.

  • 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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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.

  • 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