deldownunder
asked on
Count is slow on large database
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
Selecting a count from sysindexes does not guarantee a valid record count if usage hasn't been maintained by SQL Server...
sorry, as arbert says you need to update usage first. this is wrapped in my custom version but forgot to mention.
dbcc updateusage(0) with no_infomsgs
Of course, the updateusage command can be just as slow as selecting the count from the tables :)
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 :)
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 :)
Hmm. so i guess the long and short is that this may or may not help him depending on his specific senario. :S
Has your question been answered? If so could you please close it.
ASKER
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
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