| Subject | Re: RDB$STATISICS |
|---|---|
| Author | Adam |
| Post date | 2006-01-19T01:34:16Z |
--- In firebird-support@yahoogroups.com, "skotaylor" <skotaylor@y...>
wrote:
.html)
count, not a record count.
Record counts make sense when you have a locking database, as all
records must be relevant to all people. In fact in pretty much all of
them, the current record count is stored in a system table somewhere.
When you try to port the concept to a MGA database such as Firebird
or Postgres, you have to remember that each transaction has an
isolated view of the database.
I can't see records that have been inserted or know that records that
have been deleted unless the transactions that performed these
operations were committed before my transaction started.
So when I am counting the records, do I count records that I can see
but have already been deleted? or Do I count records that someone
else has inserted but not yet committed? You can probably see the
problem.
Firebird (and pretty much every other MGA database I am familiar
with) defines the count operation as the number of records that are
visible to your transaction. This makes the operation slow, and so
should be avoided if you don't actually need to know.
If you are testing if count(*) > 1, then replace your logic with the
exists syntax and it will go much faster.
There are some cases where the count is required, and there are
methodologies using triggers and dedicated tables that handle this
well.
http://groups.yahoo.com/group/firebird-support/message/56457
But back to the problem at hand. Sometimes you just want to know a
round-about figure. It doesn't have to be accurate, you don't really
care whether it still includes some deleted records. In this case,
the technique Simon linked to is a clever way of getting this
information.
Note, it cant work if you don't have a primary key on the table you
are counting, but otherwise, it may be a good indicator for use in a
progress bar etc.
Adam
wrote:
>indices?
> --- In firebird-support@yahoogroups.com, "Si Carter"
> <simon.carter@t...> wrote:
> >
> >
> >
> > > -----Original Message-----
> > > Is RDB$STATISICS of any use to me for tuning my database and
> > > If so, where can I get more info on what it means?(http://www.community.borland.com/article/borcon/files/3156/paper/3156
> >
> > Take a peek at this doc
> >
>
.html)
><NULL>
> This looks interesting. Thanks.
>
> > you can also use it to get approximate record counts
> > (http://www.fbtalk.net/viewtopic.php?id=164).
>
> Just for the heck of it, I tried that, and some tables display
> some with only a few records and some with over 200,000 records.Why
> is that? Neat query though.There are a couple of things of note. It is an approximate record
count, not a record count.
Record counts make sense when you have a locking database, as all
records must be relevant to all people. In fact in pretty much all of
them, the current record count is stored in a system table somewhere.
When you try to port the concept to a MGA database such as Firebird
or Postgres, you have to remember that each transaction has an
isolated view of the database.
I can't see records that have been inserted or know that records that
have been deleted unless the transactions that performed these
operations were committed before my transaction started.
So when I am counting the records, do I count records that I can see
but have already been deleted? or Do I count records that someone
else has inserted but not yet committed? You can probably see the
problem.
Firebird (and pretty much every other MGA database I am familiar
with) defines the count operation as the number of records that are
visible to your transaction. This makes the operation slow, and so
should be avoided if you don't actually need to know.
If you are testing if count(*) > 1, then replace your logic with the
exists syntax and it will go much faster.
There are some cases where the count is required, and there are
methodologies using triggers and dedicated tables that handle this
well.
http://groups.yahoo.com/group/firebird-support/message/56457
But back to the problem at hand. Sometimes you just want to know a
round-about figure. It doesn't have to be accurate, you don't really
care whether it still includes some deleted records. In this case,
the technique Simon linked to is a clever way of getting this
information.
Note, it cant work if you don't have a primary key on the table you
are counting, but otherwise, it may be a good indicator for use in a
progress bar etc.
Adam