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 ROLLBACK)"
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.