| Subject | Re: If Not SELECT COUNT(*), Then What? |
|---|---|
| Author | Adam |
| Post date | 2008-12-11T23:03:24Z |
--- In firebird-support@yahoogroups.com, "inoffensive_2009"
<larryl_hoo@...> wrote:
stored in the table header because it is different for each
transaction depending on their particular isolation level and when
they were started.
I don't necessarily agree with Helen that the problem is the working
set size, but in any case, you need to ask yourself whether knowing an
exact progress is worth the trade-off of nearly halving your performance.
In terms of avoiding count(*), firstly it is often incorrectly used as
an existence check. This is work for no good cause. You don't need to
check that count(*)>0, use exists predicate instead.
Secondly, for progress bars, I don't know of anyone who would not be
happy about a ballpark estimate. I mean windows explorer estimates
ridiculous lengths of times sometimes. People want to know that they
are about a quarter of the way through the process, not that they are
27.4%.
You can often estimate a table size and thus estimate the total count
using methods in this link.
http://www.firebirdfaq.org/faq5/
For a large enough table that doesn't get significantly purged on a
regular basis, transforming the statistics into a count should be
within 1% of accurate most of the time.
On a side note, a few years ago we submitted a version of one of our
applications for acceptance testing. There had been complaints that it
appeared to freeze at times, so we added a progress bar and the
testers were completely satisfied with the performance improvement
(there was no difference in the load times). Months later we finally
confessed that their progress bar was an animated gif.
Adam
<larryl_hoo@...> wrote:
>Count is slow because of the nature of MVCC. The count can not be
> Thanks again everybody:
>
> The spec calls for a listbox with entries for all of the
> organization's contacts, probably in the neighborhood of 10,000, but
> it could be quite a bit bigger.
>
> I'm just selecting the columns needed to fill the display, and the
> contact's key.
>
> But this is part of the specifications.
>
> This is an infrequent operation, and will only be performed once
> for each time the user wants to load the records.
stored in the table header because it is different for each
transaction depending on their particular isolation level and when
they were started.
I don't necessarily agree with Helen that the problem is the working
set size, but in any case, you need to ask yourself whether knowing an
exact progress is worth the trade-off of nearly halving your performance.
In terms of avoiding count(*), firstly it is often incorrectly used as
an existence check. This is work for no good cause. You don't need to
check that count(*)>0, use exists predicate instead.
Secondly, for progress bars, I don't know of anyone who would not be
happy about a ballpark estimate. I mean windows explorer estimates
ridiculous lengths of times sometimes. People want to know that they
are about a quarter of the way through the process, not that they are
27.4%.
You can often estimate a table size and thus estimate the total count
using methods in this link.
http://www.firebirdfaq.org/faq5/
For a large enough table that doesn't get significantly purged on a
regular basis, transforming the statistics into a count should be
within 1% of accurate most of the time.
On a side note, a few years ago we submitted a version of one of our
applications for acceptance testing. There had been complaints that it
appeared to freeze at times, so we added a progress bar and the
testers were completely satisfied with the performance improvement
(there was no difference in the load times). Months later we finally
confessed that their progress bar was an animated gif.
Adam