Subject Re: [IB-Architect] Optimizing cross-joins / aggregate-selects / no-fields from relation used - when possible ?
Author Arno Brinkman
Hi Jim,

> >> > Wouldn't it speed up increadibly when we don't fetch the linked
> >but
> >> > only do an index evaluate ?
> >
> >So the record-pointer available by the index isn't transaction dependent
> >
> The record number from the index is transaction dependent. But since
> multiple versions of that record can exist simultaneously in the
> index and your transaction can see at most one of them, the
> engine must fetch the appropriate record and verify that value
> matches the selection expression.

I saw that first is called the procedure "VIO_chase_record_version" and
after that the data is fetched. When we have only an "index-lookup" couldn't
not just exit after the VIO_chase_record_version? Is there a way that the
key-data can be different than in the index? I make an test-version and saw
that there's little speed-up, so maybe interesting enough to look if it
isn't possible to do with "only-index-lookup".

> >> So, Firebird can't optimize queries with index lookups by avoiding row
> >> retrieval.
> >
> >That would be pitty :(
> >
> Actually, no. For a database system to use an index to compute
> cardinality it must lock the entire index for the duration of
> the operation. This is a disaster in a update intensive system.
> When considering any optimization, you really must consider the
> system cost, not just query cost. One could always improve
> single user performance by locking other users for the duration
> of a query/transaction. And you could win many benchmarks by
> doing so. But the system would be unusable.

With pitty i meant there could be a speed-increment because no data-pages
had to be accessed for the unneeded data, but as i understand it must be for
the record_version. Reading a data-page looks a big cost for me.