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

Answer 2

If you need a row count, but you can live with an approximate value (not 100% guaranteed to be exact - but gives you a ballpark), you could use the system catalog views in SQL Server to achieve this like so:

SELECT 
    t.Name AS TableName,
    sum(p.rows) as RowCounts
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE 
    t.Name = 'Your Table Name'
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 

Given any table in your database, this will give you an approximation of the number of rows in that table, and it's very very fast (not measurable - less than 0.01 sec.)

Answer 3

15 million rows running on SQL Server 2005 Express

what kind of hardware are you using? your database can be set up properly, but if you're not on a good dedicated server with lots of ram, queries on a big table like that will be slow.