empire-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel Weidele <daniel.weid...@uni-konstanz.de>
Subject Re: Bulk Insert // Prepared Statement Insert
Date Thu, 23 Jul 2015 20:15:21 GMT
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 <daniel.weidele@uni-konstanz.de>:

> 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 <doebele@esteam.de>:
>
>>  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:francisdb@gmail.com]
>> *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 <doebele@esteam.de> 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:* 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
>>
>>
>>
>
>
>
> --
> *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*

Mime
View raw message