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: [email protected] [mailto:[email protected]]
to: [email protected]
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
