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 15:21:18 GMT
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(p1));
DBCmdParam p2 = cmd.addParam(column2, null);
cmd.set(column2.to(p2));
DBCmdParam p3 = cmd.addParam(column3, null);
cmd.set(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]
to: 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