[Monetdb-developers] slow insertion, why?
Fabian.Groffen at cwi.nl
Thu Jan 4 08:20:05 CET 2007
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