SQL Server 15MM rows, simple COUNT query. 15+ seconds?

Question

We took over a website from another company after a client decided to switch.

We have a table that grows by about 25k records a day, and is currently at 15MM records.

The table looks something like:

id (PK, int, not null)
member_id (int, not null)
another_id (int, not null)
date (datetime, not null)

SELECT COUNT(id) FROM tbl can take up to 15 seconds.

A simple inner join on 'another_id' takes over 30 seconds.

I can't imagine why this is taking so long. Any advice?

SQL Server 2005 Express

Answer 1

Do note that COUNT(id) will usually result in a full table scan, so it has to read the entire table to get the count. If counting is really a very important thing for you, you might want to consider creating a trigger to store the results of the count in some other table.

Without the query I can't say much about the inner join, but my guess would be that you don't have an index on either id or another_id