Prepared Statement Insert

Rainer Döbele Thu, 23 Jul 2015 10:44:52 -0700

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

re: Bulk Insert // Prepared Statement Insert

Reply via email to