COUNT(*) Statement is slow with 3+ million records

15 pts.
Tags:
SQL Server
Hi, I have a table with 30 columns, a indexed unique field and 3+ million records. When I try a simple SELECT COUNT(*) statement its take 5-7 sec. to give the output. And I am showing this output to my front end application ASP.Net taking 1 min to show the count in front end. I am doing some other tasks using this count. I am afraid that, in another 1 month the no. of records will be double and next month will be triple. I dont know how the COUNT(*) statement will perform. Can you please tell me why it is happen.? I know the no. of rows are huge. But simple count(*) should bring the total rows na.. I need to tune with slow. Please help me.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Take a look at the execution plan for your query. You’ll notice that the query is doing an Index Scan, not an Index Seek which is why the SELECT COUNT(*) takes so long. The reason for this is that the COUNT(*) function needs to look at every record in the table to make sure that the record does not contain all NULL, because if it does then the record is not counted.

As a workaround you can use the technique that Microsoft uses to quickly display the number of rights when you right click on the table and select properties.

<pre>select sum (spart.rows)
from sys.partitions spart
where spart.object_id = object_id(‘YourTable’)
and spart.index_id < 2</pre>

You should find that this returns very quickly no matter how many tables you have.

If you are using SQL 2000 still you can use the sysindexes table to get the number.

<pre>select max(ROWS)
from sysindexes
where id = object_id(‘YourTable’)</pre>

This number may be slightly off depending on how often SQL updates the sysindexes table, but it’s usually correct.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • alessandro.panzetta
    Hello Bab, did you try and perform a COUNT(ID) and check if this is slower?
    9,725 pointsBadges:
    report
  • Bab Ganesh
    Yes.. I tried. even its slow.
    15 pointsBadges:
    report
  • Denny Cherry
    That's because on the back end the COUNT(*) and COUNT(ID) and COUNT(1) is all doing an index or table scan.
    66,360 pointsBadges:
    report
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,360 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following