| Author |
Topic |
|
pug2694328
Posting Yak Master
166 Posts |
 Posted - 2006-02-02 : 13:33:47
|
| Is there anything faster thanSELECT COUNT(*) FROM TABLENAMEto get the number of rows on a table?Some of my tables have millions of rows. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-02-02 : 13:51:39
|
| What the He11 are you doing that SELECT COUNT(*) is too slow? Measuring the decay of radioative isotopes? |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-02-02 : 13:59:17
|
You can also use this assuming your table has a primary key. This method is much faster than count(*) but may not be 100 percent accurate especially at times of high contention.select rowcnt from sysindexes with (nolock) where id = object_id('<tableName>') and indid < 2Be One with the OptimizerTG |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2006-02-02 : 15:26:44
|
| Rock! Thanks TG.Blindman, I work with financial data so yup COUNT(*) can be terribly slow on our larger tables.The table that I wanted a count from had, for example, 300,064,414 rows. : ) |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-02-02 : 15:51:44
|
| Then TG's method is certainly going to be the fastest you will get, but bear in mind that it cannot be relied upon as an accurate count unless your first run UPDATE STATISTICS, which of course is going to take longer than just running count(*). |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2006-02-02 : 17:01:42
|
| Please help me understand.I tested out whether the count was accurate without using UPDATE STATISTICS by running the count query, inserting a row, and rerunning the count query.Would the problem only arise if someone was adding rows with a query and their INSERT query had not completed? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-02 : 17:08:53
|
| COUNT(*) will be accurate, however sysindexes may not due to SQL Server not updating this information.Tara Kizeraka tduggan |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-02-03 : 03:47:59
|
| It sounds like you (or someone) needs to make the decision between absolute accuracy and speed of processing. Your other alternative is to beef up your hardware so that the count doesn't take as long. 2005's partitioned tables may help, too.-------Moo. :) |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2006-02-03 : 09:21:10
|
| In this case I'm not terribly concerned about absolute accuracy as I'm looking for a change in count yes/no binary to drive logic, however it would help me to be aware of situations where I cannot rely on the count on SYSINDEXES for future use.Thanks again, works like a charm. |
|
|
|
|
Something faster than COUNT(*)