On Feb 1, 2007, at 15:03, Leyne, Sean wrote:

> The engine MUST READ read ALL records, since the index structures
> contain data values which apply to all active transactions (Ann
> forgive
> me if I'm describing this incorrectly). As such, each record must be
> read to confirm whether the data values (visible for this transaction)
> meet the query criteria.
>
> You have made an assumption regarding the format/content of the FB
> indexes and how they are processed.

Well, I think I get it now. Thanks for being patient.

What would happen if I made the database read-only?

>>> Why does it read the records?
>>>
>>> Because Firebird is a multi-generational engine, which means that
> the
>>> indexes contain data which may be 'dirty', the engine needs to read
>>> the
>>> records to confirm the data values (for this transaction).
>>
>> Jet was simply the engine I would have expected it to beat, although
>> I appreciate that it does not have the transactional overhead that FB
>> has. But MySQL 4/5, Postrges 8, SQL Server 2000/2005 and Oracle 9i/
>> 10g also outperform FB2 by a significant margin on this one test.
>
> With the exception of Postgres and Oracle none of the other databases
> are multi-generational. This makes a big difference, heck MySQL 4
> isn't
> even transactional.

I had forgotten that - IIRC you need to add some module or other.
But still, the comparison with Postgres seems valid because it is on
the same machine with the same architecture. Doing an EXPLAIN on the
same query for PG gives a plan of

"HashAggregate (cost=5807.26..5862.12 rows=5486 width=109)"
" -> Seq Scan on "Staples" (cost=0.00..5121.56 rows=54856 width=109)"

so it looks like it does the same thing. Ann said something a while
back about PG having a large RAM cache. I bumped the FB cache up to
20480 pages, which is almost large enough to hold the entire database
(4K pages, 97Mb file) but maybe "almost" is not good enough?

> Firebird like all databases has some very good points, the
> benchmarks I
> have seen shows that it outperforms Postgres for some operations, and
> it's bad points, you have found 10 out of 1000 (1% 'failure' rate
> pretty
> good as I see it, it would be better if it were .01%).

I'm not calling it a failure rate, I'm really just wondering whether
I am doing something wrong/suboptimal (that is why my OP asked about
compound indecies). I find these outliers by comparing the same test
across various engines which unfortunately results in comparisons.

> I don't think anyone took offense to your question, but perhaps to
> your
> pre-conceived notion of how FB works and the fact that you didn't
> really
> want to listen to the explanations we were providing.

I'm sorry if you felt I wasn't listening, but honestly I didn't think
I had asked the question very well.

________________________________________________________
Richard Wesley Senior Software Developer Tableau
Software
Visit: http://www.trytableau.com/now.html