Improve MSSQL Database performance (1) vs. COUNT (*) and override Count (*)

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 (*)