Problem
I need to test a SQL Server T-SQL statement and collect information on its elapsed time. How can I perform a thorough, repeatable test of my statement?
Solution
Whether you are a newbie to T-SQL or an experienced professional, you should always test your statements not only for accuracy, but also for performance. Executing a command multiple times and collecting the average elapsed time is a quick way to ensure that your code is on the right path to acceptable performance. Adjusting the statement or a database object such as an index, and then repeating the command to compare how the adjustments affect the performance is also desirable.
In this tip, we will look at a T-SQL script that contains a nested loop. The inner loop executes a command a preset number of times and the outer loop will repeat the command or set of commands another preset number of times. There are numerous comments in the script below that will explain what is happening.
For this test, I am creating a table dbo.testPeformance and I will do 100,000 inserts into the table and do this test 5 times. Then we will see how long this took for each of these 5 tests.
Setup the Test
The first thing I need to do for my test is create this table where I will test the inserts.
--Create a table for testing
create table dbo.testPerformance
(
pk int identity(1,1) primary key,
testDate date,
testInteger int,
testVarchar varchar(10)
)
go
The command that I want to test is the following and is in the larger script below.
insert into dbo.testPerformance values (getdate(), round(rand()*1000,0), 'testString')
Also, I want to truncate the dbo.testPerformance table before my next interation starts. I will use this command:
truncate table dbo.testPerformance
Code to Run the Test
Here is the code that will loop through the test.
--The outer loop - the number of times to repeat the test
declare @numberOfTests integer = 5
--The inner loop - the number of times to repeat the code
declare @numberOfIterations integer = 100000
--The following statement will display a message to the user
select 'Starting performance test' as MessageToUser
--Declare the variables
declare @overallStartTime datetime = getdate()
declare @testStartTime datetime = getdate()
declare @testEndTime datetime = getdate()
declare @testElapsedTime int = 0
declare @totalElapsedTime bigint = 0
declare @averageElapsedTime float = 0
declare @testRun integer = 1
declare @iteration integer = 1
begin transaction
--start the outer loop
while @testRun <= @numberOfTests
begin
set @iteration=1
set @testStartTime = getdate()
-----------------------------------------
--the command below is part of my test
truncate table dbo.testPerformance
--end of code
-----------------------------------------
--start the inner loop
while @iteration <= @numberOfIterations
begin
-----------------------------------------
--the command below is part of my test
insert into dbo.testPerformance values (getdate(), round(rand()*1000,0), 'testString')
--end of code
-----------------------------------------
--advance the inner loop counter
set @iteration = @iteration + 1
--commit after 1000 inserts, this can be adjusted to see how the performance is affected
if (@iteration%1000=0)
begin
commit
begin transaction
end
end
set @testEndTime = getdate()
--get the elapsed time in milliseconds for the test
set @testElapsedTime = datediff(ms,@testStartTime,@testEndTime)
--accumulate the total elapsed time
set @totalElapsedTime = @totalElapsedTime+@testElapsedTime
--display the elapsed time for the test
select 'testRun '+cast(@testRun as varchar(10)) + ', elapsed time = '
+cast(@testElapsedTime as varchar(10)) + 'ms' as MessageToUser
--advance the outer loop counter
set @testRun=@testRun+1
end
commit
declare @overallEndTime datetime = getdate()
select
@overallStartTime as OverallStartTime,
@overallEndTime as OverallEndTime,
datediff(ms,@overallStartTime,@overallEndTime) as OverallElapsedTime,
@totalElapsedTime/cast(@numberOfTests as float) as AverageElapsedTime
go
The output of the script is shown below.
Next Steps
Change the T-SQL command to experiment with different situations. You can also adjust the script as needed to display statistics that are required.
Also, please check out other T-SQL tips on MSSQLTips.com:
