Subject Re: [firebird-support] Re: Performance of Firebird vs. other DBMS
Author Martijn Tonies
> > Firstly, David was not joking when he implied that select count(*)
> > based queries is a possible weakness in any MGA database.
> Thank you and David for enlightening me.
> It is now clear to me why all SELECT COUNT(*) will be expensive,
> even the ones that I thought would do an index scan.
> I am afraid that I didn't make it clear enough that with a
> 'simple select' I meant a statement like
> These queries also perform badly, when running them together
> with the COUNT(*) queries 4 times as badly as on other DBMS.
> However, when I run only these 'simple selects', the performance
> is a mere 50% worse than that of the worst competitor.
> I guess that the extra load the COUNT(*)-statements imply will
> explain the factor 4, and the 50% may be accounted for by the
> record versioning overhead, right?
> > Firebird will not even read the index
> > if there is not a where clause to knock out some records, because
> > the index read would just be just another I/O read for no gain.
> >
> > So you don't get the impression it is a weird Firebird thing, it
> > is a problem for Postgres as well.
> >
> > (
> This is interesting.
> Firstly, in what I call the 'index scan' queries, I DO have a where
> clause involving an indexed column, and at least sometimes the
> optimizer should figure that using the index could save time.
> Secondly, one of the other databases I tested IS Postgres, and
> a) all selects, even the COUNT(*) ones, perform reasonably well, and
> b) the results of the 'index scan' queries was much better than for
> the 'full table scan' queries.
> This should not be if Postgres worked like Forebird, right?

Mind you, PostgreSQLs implementation of MGA might be different.
More "in memory" perhaps - that would make it faster. I don't know the
exact details, it might be a larger memory buffer, it might be something
else, who can tell?

One thing is for sure -- Firebird (v2) will perform better in normal
usage compared to so called benchmark applications. Why? Because
there's lies, more lies and benchmarks.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Upscene Productions
Database development questions? Check the forum!