Page 1 of 1

intermittent slowness on count() commands

Posted: Thu Aug 15, 2002 4:06 pm
by jkj2001
We've got a table that's been giving us slow count times periodically, and we don't know why.

The table has 1 million rows, and doing a select count on our unique index field can take 2-3 minutes at times. A little while later, only a few seconds.

What's really strange is that we have other tables and databases on this Solaris machine (using texis version 3.01.984591953) on which we can do simultaneous counts, and with no problems. These tables have roughly half a million rows, and never take more than a few seconds to return a count. This tells me that the CPU and disks aren't the culprits here.

Also, this one million record table has been reindexed recently (today), and we're still seeing the problem, maybe 10% of the time? Really strange, and wondering if you had any ideas on what we can check. Thanks!

intermittent slowness on count() commands

Posted: Thu Aug 15, 2002 10:12 pm
by John
The things to check would be to use time or tsql -t when you run it, and see if there is a difference in where the CPU spends it's time. The count that you are doing is identical otherwise? One possibility if that index is not used much is that it is being flushed from cache.

intermittent slowness on count() commands

Posted: Fri Aug 16, 2002 3:51 pm
by jkj2001
Thanks, John-- we'll take a look