| Subject | Re: [firebird-support] Strategy - Getting Row Count? |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2005-06-01T21:52:21Z |
sugi wrote:
specific criteria is to count them, using those criteria, or maintain a
count using some other mechanism.
for most of those applications, particularly the ones that are really
fast. I haven't told this story for a year ... Many years ago, Jim
wrote a query language that worked on ISAM files. In order to optimize
joins, he needed to know the selectivity of the index and the
cardinality of the table. The selectivity was available, but the
cardinality was not. He went to the project leader of the ISAM file
system and said, "Ed, I'd really like a way to retrieve the cardinality
of a file. Ed said that keeping the cardinality would be a performance
problem, would create a hot spot in the file, and generally wasn't going
to happen. Jim said, "Well, it doesn't have to be exact..." Ed said "13."
Unfortunately, as a general thing, when you ask a relational database
for an answer, that answer is supposed to be correct and repeatable.
it in the database without causing horrible concurrency problems.
Create a separate table MyTableCount, consisting of one integer field.
When you store a record in MyTable, store +1 in MyTableCount. When you
delete a record, store a -1 in MyTableCount. Periodically, run a
procedure that computes the sum of MyTableCount, deletes all the
existing rows, and stores the sum. When you want a count of MyTable,
select the sum of MyTableCount.
Regards,
Ann
>Unfortunately the only way to find out how many records fit some
> Firebird 1.x is usually rather slow in returning the total row count for
> a particular table/view/query. In a desktop (i.e. thick client)
> applications, this is normally a non-issue for me, but i'm running into
> a situation in web applications where this is a "must-have" feature.
specific criteria is to count them, using those criteria, or maintain a
count using some other mechanism.
>And have you noticed that the counts are pretty rough and not repeatable
> I'm sure everyone is familiar with the need to show a "paged table" UI
> where we need to show "page x of xx", "first page", "next page", etc, as
> a navigator of some sort.
for most of those applications, particularly the ones that are really
fast. I haven't told this story for a year ... Many years ago, Jim
wrote a query language that worked on ISAM files. In order to optimize
joins, he needed to know the selectivity of the index and the
cardinality of the table. The selectivity was available, but the
cardinality was not. He went to the project leader of the ISAM file
system and said, "Ed, I'd really like a way to retrieve the cardinality
of a file. Ed said that keeping the cardinality would be a performance
problem, would create a hot spot in the file, and generally wasn't going
to happen. Jim said, "Well, it doesn't have to be exact..." Ed said "13."
Unfortunately, as a general thing, when you ask a relational database
for an answer, that answer is supposed to be correct and repeatable.
>If you want the cardinality for the whole table, here's a way to store
> Considering that this view is going to be used most of the time, is
> there any tricks to make firebird returns this information in a timely
> manner? Barring that, maybe a safe and reliable way to 'cache' this
> information ?
it in the database without causing horrible concurrency problems.
Create a separate table MyTableCount, consisting of one integer field.
When you store a record in MyTable, store +1 in MyTableCount. When you
delete a record, store a -1 in MyTableCount. Periodically, run a
procedure that computes the sum of MyTableCount, deletes all the
existing rows, and stores the sum. When you want a count of MyTable,
select the sum of MyTableCount.
Regards,
Ann