How Can I Efficiently Count Rows in Extremely Large Database Tables?

Large table row count statistics optimization strategy

Counting rows in large database tables can pose performance challenges. While some articles recommend using SELECT COUNT(*) with caution, its speed is still a concern for tables with large numbers of rows and columns. This article aims to explore a database vendor-independent method for accurately counting rows in large tables.

Database vendor independent solution

The simplest and most reliable way is to use the standard COUNT(*) function. Modern database systems optimize this function even for large tables because it only needs to read enough data to estimate the number of rows. Therefore, COUNT(*) is the preferred option.

SQL Server Approximation (outside the scope of this article)

While there are some potential approximations for SQL Server, these methods are beyond the scope of this article.

Other notes

• COUNT(1) and COUNT(PrimaryKey) are equivalent to COUNT(*) in terms of row count.
• Using table partitioning can improve COUNT(*) performance on very large tables.
• If the table is updated frequently, the number of rows obtained from COUNT(*) may not be completely accurate due to pending transactions.

SQL Server Example

For a table with approximately 1.4 billion rows and 12 columns, the following query using COUNT(*) with the NOLOCK hint completed in 5 minutes 46 seconds:

<code class="language-sql">SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK)</code>

Alternatively, the following query using the system's Dynamic Management View (DMV) can be completed in less than a second:

<code class="language-sql">SELECT
   Total_Rows = SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'MyBigtable' AND (index_id</code>

(Note: The second SQL statement is incomplete and part of the code is missing from the original text)

The above is the detailed content of How Can I Efficiently Count Rows in Extremely Large Database Tables?. For more information, please follow other related articles on the PHP Chinese website!