How to test SQL statement performance to improve execution efficiency

Sometimes we often worry about inefficient execution of our SQL statements, and after repeated optimizations, we can still get no improvement.

So you can use this statement to find out where your SQL is slowing down.

Example:

SET STATISTICS io on
SET STATISTICS time on
Go
---The SQL statement you want to test
Select Top * from Tbl_cot_recstaticlist
Go
SET STATISTICS Profile OFF
SET STATISTICS io OFF
SET STATISTICS Time OFF

Display information:

SQL Server parse and compile time:

CPU time = 0 milliseconds, elapsed time = 59 milliseconds.

(100 rows affected) Table ' Tbl_cot_recstaticlist '. Scan count 1, logical read 14 times, physical read 2 times, read 992 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

SQL Server Execution Time: CPU time = 0 milliseconds, elapsed time = 306 milliseconds.

SQL Server parse and compile time: CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

SQL Server Execution Time: CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

SQL Server Execution Time: CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

Forward Original: http://www.cnblogs.com/lynnlin/archive/2008/06/18/1224376.html

In order for your program to perform more efficiently, the efficiency of SQL must not be overlooked.

The following methods are available to detect SQL execution efficiency.

For efficiency testing of multi-table queries:

1) direct from, where mode.

SET STATISTICS io on
SET STATISTICS time on
Go
---The SQL statement you want to test
Select G.grpname,t.grpid,t.flowid,t.typename,t.description
From Hygroup G, Hytype t where t.grpid = G.flowid

Go
SET STATISTICS Profile OFF
SET STATISTICS io OFF
SET STATISTICS Time OFF

SQL Server parse and compile time:
CPU time = 4 milliseconds, elapsed time = 4 milliseconds.

(8 rows affected)
Table ' Hytype '. Scan count 1, logical read 7 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Hygroup '. Scan count 1, logical read 1 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.
SQL Server parse and compile time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

2) Inner Join method:

  set STATISTICS io on
        SET STATISTICS time on
         Go
 ---The SQL statement you want to test
Select G.grpname,t.grpid,t.flowid,t.typename, T.description
from Hygroup g  inner join hytype t on   T.grpID = G.flowid       
        
        go
        SET STATISTICS profile Off
        SET STATISTICS io OFF
         SET STATISTICS time OFF

(8 rows affected)
Table ' Hytype '. Scan count 1, logical read 7 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Hygroup '. Scan count 1, logical read 1 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.
SQL Server parse and compile time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

3) Left Join method

SET STATISTICS io on
SET STATISTICS time on
Go
---The SQL statement you want to test
Select G.grpname,t.grpid,t.flowid,t.typename,t.description
From Hygroup G left join Hytype t on t.grpid = G.flowid

Go
SET STATISTICS Profile OFF
SET STATISTICS io OFF
SET STATISTICS Time OFF

SQL Server parse and compile time:
CPU time = 0 milliseconds, elapsed time = 3 milliseconds.

(10 rows affected)
Table ' Hytype '. Scan count 1, logical read 7 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Hygroup '. Scan count 1, logical read 1 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.
SQL Server parse and compile time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

4) Right Join method

SET STATISTICS io on
SET STATISTICS time on
Go
---The SQL statement you want to test
Select G.grpname,t.grpid,t.flowid,t.typename,t.description
From Hygroup g right join Hytype t on t.grpid = G.flowid

Go
SET STATISTICS Profile OFF
SET STATISTICS io OFF
SET STATISTICS Time OFF

SQL Server parse and compile time:
CPU time = 0 milliseconds, elapsed time = 3 milliseconds.

(8 rows affected)
Table ' Hygroup '. Scan count 1, logical read 8 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Hytype '. Scan count 1, logical read 1 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.
SQL Server parse and compile time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.