Fast Record Count

I think I am asking this in the right place.

I'm trying to get a record count from a table with 30million items and it takes forever.

Here is my code:

SELECT COUNT(f_id) AS 'ROWCOUNT' FROM tablename

Is there a faster way.

BTW f_id is primary key indexed.

Thanks

Alright Chap,

Try this,

use database_name

go

select object_name(id), rowcnt from sysindexes

where object_name(id) = 'tablename' and indid in (0,1)

regards

Jag

|||

Jag's method is quick, reading directly from the indexes table.

However, use caution, for just as indexes get out of sync with the data, the rowcnt in sysindexes can be wrong. So if a 'quick an dirty' report is useful (with the chance of some inaccuracy), it is a quick method.

If you desire an accurate count, there is really no other way than counting from the data table. Again counting an indexed column (or the primary key) 'could' be inexact.

|||

Ok thanks

Yes this was a million X faster.

And Arnie thanks for the input too. For this quest a close ammount is good for me. + or - a few mil is fine..lol

Thanks guys

|||To clarify Arnie's statement:

if you are using SQL2000, it is very easy for the numbers to get out-of-sync, and I would not rely on it.

If you are using SQL2005, the number should be correct, as we did fix some issues that made the number correct.

To get the number correct after you upgraded from SQL2000, you should update the statistics on the table.

Thanks,|||

Thanks Marcel,

I should have prefaced my comments with a clear indication I was referring to SQL 2000.