Select Count(*) takes time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Count(*) takes time

Author

Topic

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-29 : 04:17:03

Hello all

I use a query something like
Select Count(*) from SomeTable
in my application to get the number of rows in the table.
This query takes the most time. Any alternatives to avoid
SELECT COUNT(*) ???

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-29 : 04:29:55

instead of * use primary kay

Select Count(pk) from SomeTable

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-29 : 04:32:25

Using the PK also takes the same amount of time ...

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-29 : 04:34:24

Hi

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.

you want a quick number

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

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

-------------------------
R..

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-29 : 04:47:10

Thanks you everyone...that helped

quote:
Originally posted by rajdaksha

Hi

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.

you want a quick number

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

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

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-29 : 04:53:33

Hi

SP_SPACEUSED <TABLE_NAME>

It's return below column values

NAME
ROWS ---Here you can get the total number of records
RESERVED
DATA
INDEX_SIZE
UNUSED

if there is no non-clustered in table, there is no better
option than to scan all data pages.
-------------------------
R..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-29 : 05:12:31

Also refer other methods
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-29 : 07:41:44

Hi

Rows in SP_SPACEUSED is refering the below

SELECT ROWS
FROM SYS.PARTITIONS
WHERE OBJECT_ID = OBJECT_ID('TABLE_NAME')

-------------------------
R..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources