Original: Improve MSSQL database performance (1) vs. COUNT (*) and override Count (*)
Article Preparation database: Atricles table Data volume 60.69 million data
ArticleID primary Key Auto-increment + auto-established clustered index, Atitle nvarchar (+) acontent varchar createdate DateTime (8)
The first thing to say is: SELECT COUNT (*) from table, then count (*) and COUNT (primary key) count (text column) Efficiency comparison: Here is the test master code
DBCC FREEPROCCACHE--emptying SQLCache
SET STATISTICS io on
SET STATISTICS time on
Go
----Here is the test statement
Go
SET STATISTICS Profile OFF
SET STATISTICS io OFF
SET STATISTICS Time OFF
So let's take a look:
SELECT COUNT (*) from atricles CPU time = 1125 milliseconds, elapsed time = 1140 milliseconds.
SELECT COUNT (Atricleid) from atricles CPU time = 1093 milliseconds, occupied time = 1094 milliseconds
SELECT COUNT (atitle) from atricles CPU time = 2266 milliseconds, occupied time = 2267 milliseconds
SELECT COUNT (acontent) from atricles CPU time = 2296 milliseconds, elapsed time = 2303 milliseconds.
COUNT (*) is at the top of the count (primary key) the fastest why the fastest actually I don't know--! Guess it's possible that SQL automatically makes query optimizations.
So, do we have to COUNT (*)? No, everyone, look here:
SELECT ROWS from sysindexes WHERE ID = object_id (' atricles ') and indid = 1
So let's look at the comparison between it and the select COUNT (primary key):
First is count (primary key)
Table ' Atricles '. Scan count 1, logical read 120,368 times, physical read 3 times, read 120,364 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
SQL Server Execution Time: CPU time = 2282 milliseconds, elapsed time = 21334 milliseconds.
followed by from sysindexes
Table ' sysindexes '. Scan count 1, logical read 2 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.
sysindexes the table rowset index information for the system table is stored in this table
ID =object_id (' atricles ') ID means the table ID to which the index belongs
Indid indicates that the clustered index is found on the clustered index because the primary key was built automatically.
Rows are based on a data-level row count of indid = 0 and indid = 1, and if Indid >1, the value contains a duplicate count.
This article is to say: In the case of paging can consider using the above statement to find the data row and COUNT (*) is not inefficient thanks to the following friends advice
This article ends goodnight
Improve MSSQL Database performance (1) vs. COUNT (*) and override Count (*)