Posted by Erland Sommarskog on 02/16/06 01:13

Daniel Wetzler (Daniel.Wetzler@sig.biz) writes:
> I have to get the number of datasets within several tables in my MSSQL
> 2000 SP4 database.
> Beyond these tables is one table with about 13 million entries.
> If I perform a "select count(*) from table" it takes about 1-2 min to
> perform that task.
>
> Since I know other databases like MySQL which take less than 1 sec for
> the same task
> I'm wondering whether I have a bug in my software or whether there are
> other mechanisms to get the number of datasets for tables or the number
> of datasets within the whole database.

To perform a query like SELECT COUNT(*), SQL Server will use the narrowest
non-clustered index to count the rows. If the table does not have any
non-clustered index, it will have to scan the table. Whether it is
reasonable with 1-2 minutes for 13 MB rows, depends on several factors.
But if the rows have a high average size, say 200 MB, and the table
also suffers fragmentation, then it is not unlikely. It also matter
whether the table already is in cache or not. If SQL Server has to read
all from cache it takes some time.

If you just want a quick number, you can do

SELECT rowcnt
FROM sysindexes
WHERE object_name(id) = 'tablename'
AND indid IN (9,1)


This number may not be fully accurate, but close enough.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]