Subject Re: [Firebird-Java] performance tuning jdbc
Author Roman Rokytskyy

> Now we are working with performance tuning . On our intial
> analysis we feel performance is lower than the mysql.

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.

> This remains as a bottle neck. We tested data insertion rate
> via Jaybird's JDBC driver. Since performance tuning involves
> both firebird server and jdbc driver. I have mailed this to
> you so that you can help me , if you have any tips on
> improving performance at JDBC driver level.

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.

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.

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

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.