Daniel Wetzler (Da************ @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,
es****@sommarsk og.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx