empire-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rainer Döbele <doeb...@esteam.de>
Subject re: Bulk Insert // Prepared Statement Insert
Date Thu, 23 Jul 2015 17:43:05 GMT
Hi all,
about JDBC addBatch() and executeBatch():
Maybe this will help, but I am not sure. This is still just a bunch of individual statements.
MySQL for example has a multi row syntax for inserts like this:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
I assume only this will bring optimal performance for databases that support it.
When autocommit is off, I cannot image that simply using addBatch() and executeBatch() will
bring that much improvement over what I have suggested.
However it's worth a try and you can still use some of Empire-db to support you:
Simply don't call db.executeInsert() but use the cmd.getInsert() to get the SQL Statment and
cmd.getParamValues().
Then use a JDBC prepared statment with addBatch() and executeBatch() as suggested.
This will give you the best of both worlds.
Regards,
Rainer

P.S. Daniel please let us know, what you have found to be the best solution for you.

from: Francis De Brabandere [mailto:francisdb@gmail.com]
to: user
re: Re: Bulk Insert // Prepared Statement Insert

Would this jdbc feature not be an option to implement this?

http://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm

Cheers,
F

On 23 July 2015 at 17:21, Rainer Döbele <doebele@esteam.de<mailto:doebele@esteam.de>>
wrote:
Hi Daniel,

actually, enabling prepared statements is quite simple:
It’s a global setting on DBDatabase so just call:

db.setPreparedStatementsEnabled(true);

This will automatically make DBRecord to use a prepared statement.
However to avoid other bottlenecks you set the record values by index instead by column like
this:
int c1 = rec.getFieldIndex(column1);
...
rec.setValue(c1, value);

even more efficient is to use a DBCommand instead of a DBRecord.
This is done somewhat like this:

// Prepare command
DBCommand cmd = db.createCommand();
DBCmdParam p1 = cmd.addParam(column1, null);
cmd.set(column1.to<http://column1.to>(p1));
DBCmdParam p2 = cmd.addParam(column2, null);
cmd.set(column2.to<http://column2.to>(p2));
DBCmdParam p3 = cmd.addParam(column3, null);
cmd.set(column3.to<http://column3.to>(p3));

// insert loop
for (int i=0; i<10000; i++)
{
     p1.setValue(/* some value for column1 */);
     p2.setValue(/* some value for column2 */);
     p3.setValue(/* some value for column3 */);
     // insert
     // System.out.println(cmd.getInsert()+"-->"+StringUtils.toString(cmd.getParamValues()));
     db.executeInsert(cmd, conn);
}

This is about as fast as it can get.

However one problem remains:
Each insert is still an individual statement.
Some databases allow multiple inserts in one single statement.
There is no support yet for those kind of statements as they are non-standard and not supported
by all databases.
But it be a good idea for an improvement.

Regards
Rainer


from: daniel.weidele@gmail.com<mailto:daniel.weidele@gmail.com> [mailto:daniel.weidele@gmail.com<mailto:daniel.weidele@gmail.com>]
to: user@empire-db.apache.org<mailto:user@empire-db.apache.org>
re: Bulk Insert // Prepared Statement Insert

Hi folks,

I need to quickly insert ~100.000 rows into a data base table, ideally within a web request.

When setting auto-commit 'off' the process via DBRecord is already much faster, of course.
However, I'm wondering whether there is also support for Prepared Statements via EmpireDb,
so that I don't need to touch plain old JDBC.

Cheers,
Daniel

Mime
View raw message