How Can I Efficiently Count Rows in a Massive Database Table Across Different Vendors?

Tips for counting rows in large database tables

When dealing with very large database tables, accurately counting rows can be a challenge. Many articles point out that traditional SELECT COUNT(*) FROM TABLE_NAME methods have poor performance when dealing with large data sets. This article explores database vendor-independent row count solutions, taking into account the following specific requirements:

• Database vendor agnostic, covering MySQL, Oracle and MS SQL Server
• Pure SQL solution
• Do not use external tools or modify database design

Database vendor-agnostic solution

Unfortunately, there is currently no database vendor-agnostic solution that can surpass the COUNT(*) method and obtain the exact number of rows. Standard syntax is still the most reliable method.

SQL Server Approximate Solution

SQL Server provides some approximate solutions, but these methods are beyond the scope of this article because they cannot meet the precise counting required by the question.

Supplement: SQL Server Example

To illustrate the problem, we tested a table with 1.4 billion rows and 12 columns:

• SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK): It took 5 minutes and 46 seconds, and the counting result was 1,401,659,700
• SELECT Total_Rows = SUM(st.row_count) FROM sys.dm_db_partition_stats st WHERE object_name(object_id) = 'MyBigtable' AND (index_id

The latter returns a slightly lower number of rows due to concurrent writes during the test. Therefore, SELECT COUNT(*) is still the preferred method to get the exact number of rows.

The above is the detailed content of How Can I Efficiently Count Rows in a Massive Database Table Across Different Vendors?. For more information, please follow other related articles on the PHP Chinese website!