Re: Slow performance on count(*)?
Date: Tue, 15 Jul 2008 06:53:33 -0700 (PDT)
On Jul 14, 8:53 pm, Dion Cho <ukja.d..._at_gmail.com> wrote:
> It's interesting that "index full scan" is being used here, but it's
> quite possible.
> This is not a degenerate case, actually very common and natural case.
> The simple fact is that delete never deallocates the free space.
> Only truncate or rebuild does it.
> Dion Cho
Well, I call it a degenerate case as it would be something that would only occur in:
- An unconstrained query (or one that didn't constrain against indexed values)
- against a table with a large number number of rows
- that had had a large percentage of those rows deleted
From what has been said so far, all three of these conditions need to be true in order for you to see poor performance like this. It is is unusual for any app we write to do unconstrained queries against very large tables; it is also unusual for us to dramatically reduce the size of a particular table once it is in production via a massive delete. The only common real-world case where I can see something like this happening is in batch-processing situation where intermediate staging tables are loaded up with production data and subsequently queried, then cleaned out; however, in this case, I would expect that you would use global temporary tables for the intermediaries.
Can you think of other examples where this type of problem could arise? Our application (which has been running 24/7 since 1998 and has been upgraded many times; we've always handled our data reaping by running nightly jobs to remove the oldest day's worth of data, and we haven't run into issues with this strategy. We'd like to eventually move to using partitioned tables to make the reaping a little more elegant, but the nature of the data is such it is rather tricky to do so without a significant amount of work & migration (as new data has references to older data, so it's not a simple matter of partitioning by create date). We've had no real issues, presumably, because (1) our queries all go against indexes and (2) the daily volume on the system grows at a very slow rate (maybe 100% per year), so there isn't really an issue with "shrinkage", even though the tables probably have a fair amount of wasted space (due to holes not filling up until pages completely free up). Received on Tue Jul 15 2008 - 08:53:33 CDT