Swaranga Sarma Swaranga Sarma - 6 months ago 26
SQL Question

Fastest way to count exact number of rows in a very large table?

I have come across articles that state that

SELECT COUNT(*) FROM TABLE_NAME
will be slow when the table has lots of rows and lots of columns.

I have a table that might contain even billions of rows [it has approximately 15 columns]. Is there a better way to get the EXACT count of the number of rows of a table?

Please consider the following before your answer:


  • I am looking for a database vendor
    independent solution. It is OK if it
    covers MySQL, Oracle, MS SQL Server.
    But if there is really no database
    vendor independent solution then I
    will settle for different solutions
    for different database vendors.

  • I cannot use any other external tool
    to do this. I am mainly looking for a
    SQL based solution.

  • I cannot normalize my database design
    any further. It is already in 3NF and moreover a
    lot of code has already been written
    around it.


gbn gbn
Answer

Simple answer:

  • database vendor independent solution = use the standard = COUNT(*)
  • there are approximate SQL Server solutions but don't use COUNT(*) = out of scope

Notes:

COUNT(1) = COUNT(*) = COUNT(PrimaryKey) just in case

Edit:

SQL Server example (1.4 billion rows, 12 columns)

SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK)
-- NOLOCK here is for me only to let me test for this answer: no more, no less

1 runs, 5:46 minutes, count = 1,401,659,700

--Note, sp_spaceused uses this DMV
SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'MyBigtable' AND (index_id < 2)

2 runs, both under 1 second, count = 1,401,659,670

The second one has less rows = wrong. Would be the same or more depending on writes (deletes are done out of hours here)