Solved

Count is slow on large database

Posted on 2003-12-01
12
309 Views
Last Modified: 2012-05-04

Hi,

As part of helping our users to change their application, we perform an outomatic database reorganisation for tables that have changed. For most this is a relatively quick operation. However we have found that upto a million records takes 'less than a second ' (sorry cannot be more definitive) but a table of 140 million records takes more than 3 minutes. At (less than 1 sec) at worst it should be no  more than 2  minutes.

Any explanations  or ideas to speed up?

The command is a simple:    rec_count = count(*) from table

We could use a select rowcnt from system index tables, but then we are beholden to Microsoft to keep this feature, and it does not work for clustered index tables (or for other databases that we have to sometimes work with)

0
Comment
Question by:deldownunder
12 Comments
 
LVL 8

Accepted Solution

by:
dishanf earned 250 total points
ID: 9855762
use this...

SET STATISTICS IO ON
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table') AND indid < 2
SET STATISTICS IO OFF
GO

from..
http://www.databasejournal.com/features/mssql/article.php/1443571
0
 
LVL 34

Expert Comment

by:arbert
ID: 9855765
When you issue the above, what is the query plan?  Table scan?  Clustered Index scan?  Scan of another index.  Usually, if the query plan scans the smallest most unique index, you won't have that problem.  I can issue a count agains 70million rows and I get almost instant results when a non-clustered index is used.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9860072
What I do for fast counts is used sp_spaceused (actually i use a custom variant of it) but sp_space used is a documented SP so its functionality shouldnt change much.
0
 
LVL 34

Expert Comment

by:arbert
ID: 9860535
Selecting a count from sysindexes does not guarantee a valid record count if usage hasn't been maintained by SQL Server...
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9860615
sorry, as arbert says you need to update usage first.   this is wrapped in my custom version but forgot to mention.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9860621
dbcc updateusage(0) with no_infomsgs
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:arbert
ID: 9861410
Of course, the updateusage command can be just as slow as selecting the count from the tables :)
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9865628
Indeed.   I guess im in a fortunate position that the MSDE databases I use this method on are static apart from single daily updates as which time i call updateusage as part of the daily importing process :)
0
 
LVL 34

Expert Comment

by:arbert
ID: 9866604
Yep, I'm sure in most scenarios it would be almost instance.  However, like the data warehouse I'm loading, it takes about 2 hours to run :)
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9866627
Hmm.   so i guess the long and short is that this may or may not help him depending on his specific senario.  :S
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 10021166
Has your question been answered?   If so could you please close it.
0
 

Author Comment

by:deldownunder
ID: 10024019
Hi,
 Thanks for the pointers... Since the default set up is non-clusterd, and we need a close-nuff size (for calculating new size as we re-org the database, then we can use the sysindex count - but if not non clustered, the thigs will take time!

Appreciatethe Help
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now