Avoid using count (*) to obtain the number of table records and solve the latency problem.

Today, I encountered such a detailed problem. A colleague used sysindexes. Rows to quickly calculate the number of rows in the table to improve SQL Execution efficiency.

The result shows that the number of retrieved rows is not actually the number of rows in the table.

Just like this

To obtain the number of records in a table, the following SQL statement is generally used:

Select count (*) from DBO. Orders

However, this statement performs a full table scan to obtain the number of rows.

The following SQL statement does not perform a full table scan to obtain the number of rows:

Select rows from sysindexes

Where id = object_id ('table name') and indid <2

Many optimizations on the InternetArticleWe recommend this practice. This calculation of the rows in sysindexes is indeed faster than the calculation of the number of rows in the actual table.

However, because there are not many problems that have occurred just now, it is easy to be ignored. In fact, sysindexes is updated in a delayed manner, and the rows count may be inaccurate.

The following two methods are used to obtain the value returned by the number of rows in the same table.

We can clearly see that the returned values of the two groups are inconsistent.

People who are not careful can make a lot of jokes.

Next we will provide a solution to this latency problem:

Run DBCC updateusage (0, orders) with count_rows in the query analyzer.

Use DBCC updateusage to report and correct incorrect pages and lines in sysindexes

0: indicates the current database,

Orders: Table for report and correction

Count_rows: Specifies to use the current count of the number of rows in the table or view to update the row count column.

DBCC updateusage has many applications. This article does not dig deep into it.

For more information, see

Prompt after execution

---------------------------------------------------------------------

DBCC updateusage: The sysindexes row of the table orders has been updated (index 'pk _ pnd_orders ', partition 1 ):

Row count: the row count has been changed from (7775) to (7849.

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

---------------------------------------------------------------------

Then, sysindexes is updated in time to make it consistent with the number of rows in the actual table.

Now, I am here to write a practical article, hoping to help you.