I remember using batch inserts in the past and it yielding better performance. But that was like 7 years ago with ms sqlserver
F On 23 July 2015 at 19:43, Rainer Döbele <[email protected]> wrote: > 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]> 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(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 > > >
