The database that is commonly used in the website construction language ASP.NET MSSQL, how to check the execution time of SQL statements in the use of MSSQL, we will test it with SQL statements below, and get its execution efficiency, so that website coders can better grasp it. Our approach is to set STATISTICS so we can see what the system is like when executing SQL. The options are PROFILE, IO, TIME. The details are as follows:
SET STATISTICS PROFILE ON: Displays the time in milliseconds it takes to analyze, compile, and execute queries.
SET STATISTICS IO ON: Reports information about the number of scans, logical reads (pages accessed in the cache), and physical reads (the number of times disks were accessed) for each table referenced within the statement.
SET STATISTICS TIME ON: Displays the result set after each query is executed, representing the configuration file of the query execution.
To use: Open SQL SERVER Query Analyzer and enter the following statement:
SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO - your SQL script starts
SELECT [LastName] FROM [persons] - your own SQL statement
GO -- Your SQL script ends
SET STATISTICS PROFILE OFF
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
After using the above code, it will be executed to get a result display page, and you can find the corresponding answer in the result display.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 5 ms.
(1 row(s) affected)
Table 'sp_UserAssets'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sp_RealNameAuthentication'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sp_UserInfo'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(6 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 384 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
|
Posted on 1/11/2016 11:54:08 AM
|
|
|
|
