Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 21, 2004

Benchmarking Performance of a Query - Part 1 Elapsed Time - Page 2

By Gregory A. Larsen

Another method to get the elapsed time would be to use the SQL Server "SET STATISTICS TIME ON" command. This command will toggle ON a SQL Server time gathering process for the current SQL Server connection. Once you turn ON the statistics gathering process SQL Server will produce time statistics for each subsequent command run for the existing connection. To turn off the statistics gathering process for a given connection you must run the "SET STATISTICS TIME OFF" command.

Turning on the time statistics will display the, compile, CPU and elapsed time for each command processed. I sometimes find this command useful, although this statistics gathering process sometimes produces lots of output and increases the execution time under a number of different situations. Because of these drawbacks, I find this method does not work well for queries that process many commands, or contain scalar functions. Here is an example of some code that uses the "SET STATISTICS TIME ..." command to toggle ON and OFF the time statistics gathering process.

set statistics time on
select 'How many Order records are there? ', count(*) 
   from Northwind.dbo.Orders

declare @x int
set @x = 0
while @x < 2
begin
  set @x = @x + 1
  select top 1 OrderID ProductID from Northwind.dbo.[Order Details]
end
set statistics time off

Here is the output produced from this code. Note that this code produces statistics output for each and every T-SQL statement. Therefore, this code works well for a single T-SQL statement, but produces multiple lines of output as it processes the WHILE loop, which causes me to normally not use this method for determining elapsed time.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
                                               
---------------------------------- ----------- 
How many Order records are there?  830

(1 row(s) affected)


SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
ProductID   
----------- 
10285

(1 row(s) affected)


SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 5 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
ProductID   
----------- 
10285

(1 row(s) affected)


SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

At the beginning of my article, I talked about how you might go about determining how writing code one way is better than writing code another way. The way I do this is to use an elapsed time benchmark test of each different coding method. Let's go through an example of how I do this.

For my example, I will take two different methods of padding a character field that contains an integer value with zeroes. If you look out at my web site you will see I have two different methods for padding with zeroes. The code for the first method looks like this:

DECLARE @I INT
DECLARE @C CHAR(8)
SET @C = ''
SET @I = 123
SET @C = RIGHT(@I+1000000000000000000,DATALENGTH(@C))
PRINT @C

The second method has code that looks like this:

DECLARE @I INT
DECLARE @C CHAR(8)
SET @C = ''
SET @I = 123
SET @C= REPLICATE('0', DATALENGTH(@C) - 
DATALENGTH(@I)+ 1) + CAST(@I AS CHAR)
PRINT @C

If you run these two chunks of code, they run very fast, less than 1 ms. Since each query runs so quickly, how might you determine which one is more efficient? Well if you use the method I showed above, and execute each section of code 1,000,000 times you should get an indication which one performs faster (shorter elapsed time) than the other. Here is the code I wrote that runs each one of the padding with zeroes example a million times.

DECLARE @I INT
DECLARE @C CHAR(8)
DECLARE @ST DATETIME
DECLARE @X INT
SET @C = ''
SET @I = 123
SET @X = 0 
SET @ST = GETDATE()
WHILE @X < 1000000
BEGIN
  SET @C = RIGHT(@I+1000000000000000000,DATALENGTH(@C))
  SET @X = @X + 1
END
PRINT 'FIRST METHOD COMPLETED IN: ' + 
      RTRIM(CAST(DATEDIFF(SS,@ST,GETDATE()) AS CHAR(10))) +
      ' SECONDS!'
SET @C = ''
SET @I = 123
SET @X = 0 
SET @ST = GETDATE()
WHILE @X < 1000000
BEGIN
  SET @C= REPLICATE('0', DATALENGTH(@C) - 
          DATALENGTH(@I)+ 1) + CAST(@I AS CHAR)
  SET @X = @X + 1
END
PRINT 'SECOND METHOD COMPLETED IN: ' + 
      RTRIM(CAST(DATEDIFF(SS,@ST,GETDATE()) AS CHAR(10))) +
      ' SECONDS!'

When I run this code on my machine, I get the follow results:

FIRST METHOD COMPLETED IN: 19 SECONDS!
SECOND METHOD COMPLETED IN: 13 SECONDS!

This shows that the second method of padding with zeroes runs faster than the first example. When you have more than one way to do something that runs extremely fast and you want to know which method is faster, then this method is useful to benchmark your code to determine which one is faster.

Conclusion

This article on benchmarking performance only discussed different methods of how to measure elapsed time. I know this article did not go into detail on how you might be able to use these techniques to optimize slow running queries. However, I hope these examples gave you a number of different ways you can use Query Analyzer to determine how long a particular query runs. Once you know how long your query consistently runs, then you can start modifying the query to determine what causes the query to run slow. This will allow you to try different techniques to see if you can reduce the time it takes to process a slow running query. The next article in this series will discuss methods of benchmarking I/O and CPU resources.

» See All Articles by Columnist Gregory A. Larsen



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date