Hi Rainer & Francis, please forget about my last mail - UPDATE_TIMESTAMP still works like a charm - I actually made a little copy-paste mistake, since I have to deal with 3 prepared insert statements within the same loop.
So one of my largest amounts of data to persist is about 20 MB - that now takes 0.3 seconds :) Thank you VERY much for the help, EmpireDB rocks! Cheers Daniel 2015-07-23 15:56 GMT-04:00 Daniel Weidele <[email protected]>: > Hi Rainer & Francis, > > thanks for your feedback. > > I am now about to check out Rainer's suggestion with DBCommand, however, > the cool feature with the UPDATE_TIMESTAMP does not work out of the box. > > I guess I therefore need to add this explicitly: > cmd.set(T_NODE.C_UPDATE_TIMESTAMP.to(<?>)); > > Is there any EmpireDB equivalent to NOW(), CURDATE(), or CURTIME() that I > could plugin for <?> > > Cheers, > Daniel > > 2015-07-23 13:43 GMT-04:00 Rainer Döbele <[email protected]>: > >> 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 >> >> >> > > > > -- > *Daniel K.I. Weidele, **M.Sc. B.Sc.* > *PhD student* > *ERC Synergy-NEXUS 1492 <http://www.nexus1492.eu/>* > *Algorithmics Group* > *Department of Computer & Information Science* > *University of Konstanz* > *PO Box 67* > *78457 Konstanz* > *mobile. +49 (0)174 7275725 <%2B49%20%280%29174%207275725>* > *office. +49 (0)7531 88-4571 <%2B49%20%280%297531%2088-4571>* > -- *Daniel K.I. Weidele, **M.Sc. B.Sc.* *PhD student* *ERC Synergy-NEXUS 1492 <http://www.nexus1492.eu/>* *Algorithmics Group* *Department of Computer & Information Science* *University of Konstanz* *PO Box 67* *78457 Konstanz* *mobile. +49 (0)174 7275725* *office. +49 (0)7531 88-4571*
