COUNT and COUNT_BIG are too slowly, what to do?!

sseerraajj

hello..
I used COUNT_BIG to get the number of rows but it was too slowly.
I have 100,000 rows in the table.

select COUNT_BIG (*) from topicstbl where partID like '" & PagePartID & "'

the first execute for this query taked 5 secondes. and in another times it taked 1 second.

this mean when the rows count will be 1 million it will take 10 secondes or more!

what I have to do

oj


The reason for using count_big() is that you expect your returned count value exceeds INT limit. If your table is not that large there isn't a need for count_big().

The perf between using count() vs count_big() on this small table should be nearly identical. The first query should take the longest which is natural and expected. Later queries benefit from the first because the data has cached.

It's absolutely wrong to correlate 1 second for 100K and 10 seconds for 1M. It does not work in such linear fashion. The length it takes to compute the count is all depending on the system at that time. If the system is busy and there is no cache, it can take forever.

Marcel van der Holst - MSFT

Would it be possible for you to describe your table (i.e. what are columns, etc) and if you are using SQL2005 or SQL2000.

If you have a lot of data pages, SQL Server has to do a scan of all these pages when it does a count(*) or count_big(*). So the larger the data, the longer it will take to do the count.

Thanks,