Subject Re: [Firebird-Java] performance tuning jdbc
Author karthick srini
Dear Roman ,

thanks for your response. We tried your suggestions
but it don't has boosted the performance. Herewith we
made our response inline with your reply,

> People say it is lower. Since I'm not using MySQL, I
> cannot neither confirm
> nor deny this. I think MySQL has right to claim that
> it is the fastest
> database, they earned that paying the price of being
> non-transactional, etc.
> But other reports say that in many cases Firebird is
> as fast as Oracle,
> sometimes even faster.

We have used Mysql's InnoDB engine. It supports
transaction and FK. Even compared to Mysql's InnoDB
firebird's insertion rate performance is lower (1.5
times in preapred statementa and batch , in case of
ten thousand records). Direct statement insertion is
of the order of 3 times that of mysql for 10000
records. Our scenario is of mainly of insertion. But
in firebird selection is pretty fast since it supports
configuration of cache.

> First of all, no auto-commit mode. Or more
> correctly, no separate inserts in
> auto-commit mode. Either use batch updates (in this
> case complete batch is
> executed in one transaction) or use explicit
> transaction management. Keep
> batch size between 1,000 and 5,000-10,000 inserts.

We used transaction in batch size you have specified
(5k, 10 k etc). But even insertion rate is less

> Also you can improve the performance by using the
> Type 2 JDBC driver when
> connecting to the server running on the local
> machine
> (jdbc:firebirdsql:local URLs). Even more, you can
> use embedded server
> driver, but in this case you have to have an
> exclusive access to the
> database on local file system.
But type 2 driver has not improved the insertion rate

> Try tuning the socket buffer size when using the
> type 4 JDBC driver.

Since it remains as System configuration I tested
with default. Used the same for both mysql and

> Also consider using external tables. This has almost
> nothing to do with the
> JDBC driver, but people say that the insertion rate
> can be up to 10 times
> higher compared to the API calls.

External tables cannot be created for tables having
FK or PK. But our application has both. So we are
unable to use that option.

Herewith I have attached my test program for your

Thanks again for your inputs,

Thanks & Regards,

Do you Yahoo!?
Shop for Back-to-School deals on Yahoo! Shopping.

[Non-text portions of this message have been removed]