June 7, 2005 at 1:44 am
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?
June 10, 2005 at 8:00 am
This was removed by the editor as SPAM
June 15, 2005 at 7:01 am
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
🙂
June 16, 2005 at 10:46 pm
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.
June 17, 2005 at 12:07 am
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