473,215 Members | 1,244 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,215 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 23657
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Rich | last post by:
My table looks like this: char(150) HTTP_REF, char(250) HTTP_USER, char(150) REMOTE_ADDR, char(150) REMOTE_HOST, char(150) URL, smalldatetime TIME_STAMP There are no indexes on this table...
4
by: Ben | last post by:
My question, pulled out of my source code comments: // This search is SLOW when WHERE is just zcustnum. This is inexplicable // to me, as the WHERE and ON conditions only reference zcustnum...
6
by: Rory Campbell-Lange | last post by:
The following query on some small datasets takes over a second to run. I'd be grateful for some help in understanding the explain output, and to remake the code. Looks like the sort is using up...
1
by: aleicaro | last post by:
Hi, I have a problem with db2 query... I access to my database via jdbc using com.ibm.db2.jcc.DB2Driver. I build my string query in a java class. I have some parameters in my query, but there are...
3
by: ret4rt | last post by:
Hello. I have a database with movies similar with imdb and i want to find out which directors have directed both thriller and drama movies. The output i want is like this "DIR_NAME,amount of...
0
by: =?Utf-8?B?VG9t?= | last post by:
I have a number of printers on my home office network, running XP Pro. Two are networked and the other two are parallel port. Everything works, but lately it seems like when I select a printer from...
1
by: viki1967 | last post by:
Hi. I have a table in a mysql db: NAME ROLE DATE TYPE MARK STR 2008-04-28 B KARL MAN ...
3
dcharnigo
by: dcharnigo | last post by:
Hi All, I am running a sql query from a web application. The web application timeout is set to 30 seconds and really should not be increased. I am running a very specific query . The table...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

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.