14 December 2011

SQL Performance Tips 5

Hello,

Another SQL Performance tip, which will help you to determine the exact execution time of your sql statements. The simplest way of doing that is to save the time before running your statement and then compare that value with current time. Here is a sample SQL statement:


DECLARE @date1 DATETIME, @date2 DATETIME
SET
@date1 = GETDATE()

SET NOCOUNT ON

SELECT
MAX(num) FROM table1 ORDER BY some_date_column



SET @date2 = GETDATE()
PRINT CONVERT(VARCHAR(12),@date2 - @date1,114)


SET @date1 = GETDATE()


SELECT MAX(num) FROM table1


SET @date2 = GETDATE()
PRINT CONVERT(VARCHAR(12),@date2 - @date1,114)


The results will be in the following format:


00:00:01:547
00:00:00:017

Of course you could use the SET STATISTICS TIME { ON | OFF } but that will return a big mess when you have a SPROC which contains a bunch of sql statements.

Happy Coding ! ExtremeDev makes programming easier !!!

No comments:

Post a Comment

your thoughts are welcome:

Post a Comment

Need more? Leave comments and subscribe to my blog.

.