471,108 Members | 1,312 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

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

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

Feb 15 '06 #1
5 23493
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.

Feb 15 '06 #2
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
Feb 15 '06 #3
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

Feb 16 '06 #4
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
Feb 16 '06 #5
i'll bite. why do you need to know the number of rows????

Feb 26 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Rich | last post: by
4 posts views Thread by Ben | last post: by
6 posts views Thread by Rory Campbell-Lange | last post: by
1 post views Thread by aleicaro | last post: by
reply views Thread by =?Utf-8?B?VG9t?= | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.