Subject Re: [firebird-support] Re: select count(*) took time
Author Lester Caine
Hoang-Vu PHUNG wrote:

> Thanks for your answer and your tips.
> The thing is select count(*) on an oracle database
> ist many times faster. And more strange, select
> count(*) on a hsql database (database written entirely
> in java) is also faster.
> I wrote an application thats inserts data in databases
> via jdbc. It took around 1.5 seconds for 100 rows
> (really big record) on hsqldb and nearly 8 seconds !!
> on firebird (with jaybird version 2.0). [I used
> jython]

Inserts depend on a number of things, and the spread of insert times
will vary greatly. Some databases do one type of data faster than another.

> It is quite impossible to understand when I heard a
> site using a firebird database of 47 Gbytes !!

Without any information it is impossible to comment, but the first point
would be - never use COUNT(*) on a fully transactional database. I run
triggers to maintain key counts against the COMMITTED data and just look
up on that table when required.

Indexes on tables can take times to update, and so inserts may need to
be managed in a different way. Bulk inserts are best done with the
indexes switched off, and a single update to the indexes when the upload
is complete.

It is possible to make SQL queries that are very slow or very fast
depending on how the data is accessed. Microsoft are very good at
writing tests that work well on MSSQL and badly elsewhere. Just change a
few bits, and the reverse is true. So the overall performance of YOUR
application matters, and then it is a matter of seeing what is slowing
things down and dealing with that.

Lester Caine - G8HFL
L.S.Caine Electronic Services -
Model Engineers Digital Workshop -
Treasurer - Firebird Foundation Inc. -