Stored Procedure runs faster in a transaction

  • I have a stored procedure that took 5 minutes to execute. Running profiler showed that it was recompiling (because of temporary tables).

    While trying to eliminate the recompilation, I wrapped the call of the stored procedure in a BEGIN TRAN/ROLLBACK TRAN. Suddenly, the execution dropped to 4 seconds!!

    i.e.

    "EXEC sp_name" takes 5 minutes

    "BEGIN TRAN EXEC sp_name ROLLBACK TRAN" takes only 4 secs

    The difference seems to be in the time taken to recompile. Without the BEGIN/ROLLBACK, each recompilation was taking about 1min, but with the BEGIN/ROLLBACK, each recompilation takes under 1sec.

    The same improvement is seen if I use BEGIN and COMMIT. Also, if I put BEGIN TRAN as the first statement inside the stored procedure, and COMMIT TRAN as the last statement, I see the same improvement.

    I can't post any code, but can anybody explain why I am seeing such a dramatic improvement?

     

     

  • This was removed by the editor as SPAM

  • Some of the points below might lead to the answer :-

    1. ISOLATION LEVEL of the query

    2. if the Stored Proc is using many INSERT/UPDATES in it, it might worth looking into the lock/latch level. Is the locking getting escalated because of ISOLATION LEVEL and too many locks.

    3. Concurrency of data access during the time frame while running the SQL QUERY.

    regds/ramanuj


    🙂

  • That's weird!!

    The only thing that I can think of is data may still sit in cache when you re run the proc will run in  seconds.

     

     

  • Grasshopper,

    I eliminated any possible caching differences by running dbcc freeproccache and dbcc dropcleanbuffers first.

    ramanuj,

    I am running the procedure on a machine that is only used by me, so locking and blocking should not be an issue.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply