intermittent slowness on count() commands

Post Reply
jkj2001
Posts: 142
Joined: Fri Mar 29, 2002 1:39 pm

intermittent slowness on count() commands

Post 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!
User avatar
John
Site Admin
Posts: 2622
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

intermittent slowness on count() commands

Post 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.
John Turnbull
Thunderstone Software
jkj2001
Posts: 142
Joined: Fri Mar 29, 2002 1:39 pm

intermittent slowness on count() commands

Post by jkj2001 »

Thanks, John-- we'll take a look
Post Reply