Subject Re: select count(*) took time
Author Adam
--- In, Hoang-Vu PHUNG
<hvu_phung@...> wrote:
> Hi Adam,
> 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]

Insert is VERY different to count(*). I can't comment how Oracle works
for count(*), but I know it is not apples and apples. As far as the
insert speed goes, is it over a LAN or on the local machine? One of
the known places where Firebird can be improved is the inefficency of
the over-the-wire protocol. Do you prepare once then pump, or are you
preparing for every inserted record? Are you creating a transaction
per inserted value, or are you pumping the data as a single
transaction? Are there any triggers that would cause the dbms to do
more work than you had anticipated?

I do not know if you are using Jaybird correctly, perhaps you could
ask specific questions on the appropriate list. 8 seconds for 100 rows
is very slow, normally I get around 1000 rows per second if I need.

If you think a query is slow, try it first in iSQL. This will tell you
whether it is to do with Firebird or your interface. Then ask on
either this list if it is Firebird, or the jdbc list if it is the