VB and VBA Users Source Code: Simple tip on debugging your stored procedures
[
Home
|
Contents
|
Search
|
Reply
| Previous |
Next
]
VB/VBA Source Code
Simple tip on debugging your stored procedures
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Tuesday, October 15, 2002
Hits:
1067
Category:
Database/SQL/ADO
Article:
I am adding this article, primarily for myself as I have writen this block of code a millions times. Everytime I am asked to optimise a stored proc., the first step I take is to add an optional parameter to the procedure called @debug and then I mark every select, insert, update and delete with print of the time taken, eg: create procedure MyTestProc ( --All other params here, then add: @debug char(1) = "N" ) as --Add these params declare @now datetime, @diff float --Initialise the @now variable select @now = getdate() .... A select/delete etc statement --Then after every action add the following if @debug = "Y" begin select @diff = datediff(ms, @now, getdate()) select "Step 1 " = @diff select @now = getdate() end .... A select/delete etc statement --Then after every action add the following if @debug = "Y" begin select @diff = datediff(ms, @now, getdate()) select "Step 2 " = @diff select @now = getdate() end Now run the procedure and set @debug = "Y" and review the results to find out which actions are taking the longest and why...
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
