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.
| Author |
Topic |
|
daibaocun
Starting Member
9 Posts |
 Posted - 2011-01-21 : 10:02:06
|
| 1. MSSQL 2000 database has around 20 million records.2. A simple select count statement takes about 15 minutes, although proper indexing has been created on the table and fields.how to improve the preformance the simple select count(*) statement execute?the server specs. for MSSQL 2000 database:- Intel XEON - 4-CPU Quad Cores- 8-GB of physical memory. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2011-01-21 : 22:41:33
|
| Select count(*) is simple to write code but not simple to execute. Do you have a non-clustered index on that table? |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-22 : 03:29:37
|
| Count has to touch all qualifying rows in the table. On 20 million rows that'll take a while, especially if there's locking. It will chose, if it can, the smallest nonclustered index on the table (not the cluster unless there are no other indexes)Do you need a rough count of all rows in the table? If so, consider querying sys.partitions rather than counting yourself.--Gail ShawSQL Server MVP |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-22 : 08:05:11
|
SQL 2000 or 2008? You posted in the 2008 section, but said 2000.If 2008, use sys.partitions as Gail said: SELECT rows FROM sys.partitions WHERE object_id = object_id('TableNameHere'); |
|
|
|
daibaocun
Starting Member
9 Posts |
Posted - 2011-01-22 : 09:58:59
|
| It is SQL server 2000. The topic should be put under "SQL Server 2000" Forums. Sorry for the confusing. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-22 : 10:18:12
|
You can do this:select rowcnt from sysindexes where id = object_id('YourTable') and indid in(0, 1) as Gail says, consider it a rough count as opposed to an exact count (though often times it will be exact). |
|
|
|
daibaocun
Starting Member
9 Posts |
Posted - 2011-01-31 : 04:05:40
|
| After create clustered index on the table, it takes about 2 seconds to execute select count statement against 17 million records. thanks. |
|
|
|
|
|
|
A simple select count statement takes about 15 min