quickest way to count all records in table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Old Forums

CLOSED - General SQL Server

quickest way to count all records in table?

Author

Topic

jjasper
Starting Member

25 Posts

Posted - 2006-08-14 : 16:29:33

Hi,
I am doing a "select count(*) from my_table" to count all the records in the table. Now, there are over 85 milion records so it is taking about 16 minutes to count.

Is there any other way to find out how many records in my table ? maybe not through SQL but some other method ?

Thanks,
John

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-14 : 16:47:43

Try


DBCC UPDATEUSAGE ('MYDB_Name')

SELECT o.[name], i.rowcnt
FROM sysobjects o
LEFT OUTER JOIN sysindexes i
ON o.[id] = i.[id]
WHERE o.xtype = 'U' AND i.indid < 2 and o.[name] = 'UrTbl'

Srinika

jjasper
Starting Member

25 Posts

Posted - 2006-08-14 : 16:57:33

WOW ! Much, much quicker

Thank you !

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-14 : 19:22:18

Quicker but not accurate.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-14 : 19:46:58

quote:
Originally posted by LoztInSpace

Quicker but not accurate.

It may be good enough for government work.

CODO ERGO SUM

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-14 : 20:47:20

DBCC UPDATEUSAGE can also cause a lot of blocking and be very disruptive when you have a large database.

Just use the NOLOCK hint with your COUNT(*) statement. That will probably be as accurate as the sysindexes method and you won't have the blocking to deal with.

-ec

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 02:34:14

I am more concerned that a SELECT COUNT(*) takes 16 minutes to finish for only 85 million rows...

Peter Larsson
Helsingborg, Sweden

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-15 : 02:42:59

quote:
Originally posted by Peso

I am more concerned that a SELECT COUNT(*) takes 16 minutes to finish for only 85 million rows...

Peter Larsson
Helsingborg, Sweden

have you timed DBCC UPDATEUSAGE?

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-15 : 02:44:04

also, do you have a primary key on this table? count will be much faster with a PK in place.

-ec

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-15 : 03:54:04

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by LoztInSpace

Quicker but not accurate.

It may be good enough for government work.

CODO ERGO SUM

I wish I worked for your government. I can spend hours of my life with a governmental client trying to explain why numbers from various reports don't reconcile. (If they were the same thing they would be on the same report. Repeat until suicidal. They care!)

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-15 : 10:21:31

For S & G, i made a table and shoved 150 million records into it.


if exists (select * from sysobjects where id = object_id('tbltest') and sysstat & 0xf = 3)
	drop table tbltest
GO

CREATE TABLE tbltest (
	PeeKay int IDENTITY (1, 1) NOT NULL ,
	Testboy int NULL ,
	Extra int NULL ,
	Nums int NULL ,
	Foos varchar (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		PeeKay
	)
)

Took about 20 mins to loop around and shove in the records. First a million, then 2 million, etc until i had 150+ million. Then i did this 3 times

SELECT COUNT(*) FROM tbltest

RESULTS:
152679088 (1:54)
152679088 (1:48)
152679088 (1:53)

This is on my happy little workstation with MSDE installed. P4 3.2, 1 Gig ram.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

jjasper
Starting Member

25 Posts

Posted - 2006-08-15 : 14:10:05

Why is Srinika's solution not accurate ?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-15 : 14:22:38

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=7881

Srinika

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-15 : 14:22:43

quote:
Originally posted by jjasper

Why is Srinika's solution not accurate ?

Because sysindexes can be inaccurate. Running DBCC UPDATEUSAGE corrects the inaccuracies, but Srinika's query is only guaranteed to be accurate if no data changes have occurred since DBCC UPDATEUSAGE was run. Check DBCC UPDATEUSAGE in SQL Server Books Online for details.

Tara Kizer

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-08-15 : 14:56:14

DonAtWorks time estimates may reflect caching. I administrate a system where the developers put in a count(*) from a large table and the first time the app runs in the morning it takes 30+ seconds, but after that the queries take less than a second. So I'd be surprised if the 1:54 estimate was for the first time because the second and third times were so close to it.

In my experience checking sysindexes instead of doing count(*) provides the same "logical" functionality. Application code has different paths to take based on the numbers. "If the number of rows is less than a thousand then do 1 thing, if it is more than a thousand do something else." Or "If there are any rows at all, then do 1 thing, else do something else. There probably are reasons that an application needs to know the exact, up to date, real time count for a table, I just haven't come across it yet. "Close enough for Government work" is probably good enough for most work when the "real time" count costs end users tons of time as a result of poor development queries not thinking about performance.

Just my opinion,
Dalton

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-08-15 : 15:22:39

What about sp_spaceused table_name?

Sanjeev Shrestha
12/17/1971

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-15 : 15:32:26

quote:
Originally posted by sanjnep

What about sp_spaceused table_name?

Sanjeev Shrestha
12/17/1971

Same issue.

Tara Kizer

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-15 : 19:24:34

quote:
Originally posted by druer

In my experience checking sysindexes instead of doing count(*) provides the same "logical" functionality. Application code has different paths to take based on the numbers. "If the number of rows is less than a thousand then do 1 thing, if it is more than a thousand do something else." Or "If there are any rows at all, then do 1 thing, else do something else.

I agree sort of. These kind of things can make a difference when you have no predicate for your count. Bear in mind though that mostly you don't use count like this. More often the count is taken in the context of a transaction and is unlikely to be looking at the entire table but only some related entities. For example, give me the account numbers and balance for accounts that have had at least one credit today. You need an accurate result taken in the context of right now, not approximate and there is no other way of doing this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources