[Monetdb-developers] slow insertion, why?

Fabian Groffen Fabian.Groffen at cwi.nl
Thu Jan 4 08:20:05 CET 2007

Hi Eric,

On 03-01-2007 22:59:26 -0800, eric6251 wrote:
> Dear Sir/Madam:
>    After creating a table in MonetDB, I insert data by running "insert into
> table ..." on the client via JDBC(monetdb-1.5-jdbc.jar) , but the insertion
> speed is too slow(100rows per 5 sec.).
>    I guess the reason is probably that MonetDB treats every insert clause as
> a transation thus performs 'commits' afterwards for every single insert
> operation. Is that so?

Correct.  This is called auto-commit, and is enabled by default.
Turning it off is done using Connection.setAutoCommit() method.

>    To fix the problem, I then group the insert clause(400 per group) and
> self-write a commit after the 400 insert clauses, but error occurs: "COMMIT:
> not allowed in auto commit mode" and "current transaction is aborted (please

In auto-commit mode, you cannot commit ;)  You could get this working by
switching off auto-commit, but if you're after speed, you can do these
batches of 400 statements also through the batching interface of JDBC,
which allows you to gain some speed.  I think you should look into that.
In combination with a PreparedStatement, batching can be a very nice
method of inserting data.  Look for the addBatch(), executeBatch() and
setXXX() methods of the (Prepared)Statement classes.

More information about the developers-list mailing list