very slow query (select count(*) from table)

[Daniel Wetzler]

Dear MS SQL Experts,

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.

Can you give me some hints ?

Best regards,

Daniel Wetzler

[joeNOSPAM]

If you don't already have a unique index on this table, making an
index of that sort would help. The smaller the index (eg: on an int
column) the better.

[Erland Sommarskog]

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****@sommarskog.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

[Daniel Wetzler]

Dear Erland and Joe,

thank you very much.
The select on sysindexes is a great advice and the best solution for
my
problem.

I tried another workaround yesterday :

I used the following statement :

select count(1) from table. (for tables with PRIMARY KEY in first
column)

It seems that MS SQL has to load the whole table information if I say
count(*).

Best regards and many thanks,

Daniel

[Erland Sommarskog]

Daniel Wetzler (Da************@sig.biz) writes:

I used the following statement :

select count(1) from table. (for tables with PRIMARY KEY in first
column)
COUNT(1) or COUNT(*) makes no difference.
It seems that MS SQL has to load the whole table information if I say
count(*).

As I said, if there is no non-clustered table, there is no better
option than to scan all data pages.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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

[Doug]

i'll bite. why do you need to know the number of rows????