Hi, I've implemented the import using batch inserts and it was absolutely worth it. Tests on three very different machines and sapdb installations (7.3 and 7.4) showed an improvement of insert performance roughly by a factor of 3.
Thank you very much for your explanations. Best regards, Robert On Thursday 17 July 2003 16:06, you wrote: > Hello Robert, > > > Thanks for the quick answer. I have mostly very small records > > ( a few hundred > > bytes) and a packet size of 36864, so many records should fit > > in there. Do > > you have a rough estimate what I could gain (best case). I'm > > not going to > > implement batch inserts into our database tool if the best I > > can expect is > > something like a 10% performance gain, > > I was a little bit curious itself, and checked it with a simple table > (an int, char(128), and a timestamp). > > Inserting 30000 rows did take me about 20 seconds when done row by row > on my machine, and about 8 seconds using an executeBatch(). > > However, these numbers may of course vary on your machine, so there > is no warranty. > > Be prepared that the java application that makes that batch insert > becomes a memory hog, so if that application starts swapping due to > that fact, you better go with single inserts. > > > if on the other hand I lose > > > fine-grained control over error handling (which would then be > > per batch and > > not per record, if I'm not mistaken). > > Indeed you should look at my mail from yesterday, which goes a little > bit into the details here: > > http://listserv.sap.com/pipermail/sapdb.general/2003-July/022327.html > > - it is not the same as in single insert, but there is something useful. > > > Thanks in advance, > > > > Robert > > > > On Thursday 17 July 2003 14:23, you wrote: > > > Very roughly, you can: > > > > > > - compute the size of the data to be inserted > > > (see > > > > http://www.sapdb.org/7.4/htmhelp/ff/baaba8d55a11d2a97400a0c944 > > 9261/content. > > > > >htm ) > > > > > > - divide database parameter _PACKET_SIZE through that number to get > > > (estimated) the number of rows that fit into one packet. > > > > > > Having that number, you may get a performance gain if the > > > > number is high, > > > > > so expect not so much for e.g. LONG data. > > > > > > You may of course experiment with other settings of > > > > _PACKET_SIZE, if your > > > > > application generally does large batch inserts. > > > > > > Alexander Schr�der > > > SAP DB, SAP Labs Berlin > > > > > > > -----Original Message----- > > > > From: Robert Krueger [mailto:[EMAIL PROTECTED] > > > > Sent: Thursday, July 17, 2003 2:03 PM > > > > To: [EMAIL PROTECTED] > > > > Subject: perfomance gain through JDBC batch inserts > > > > > > > > > > > > > > > > did anyone experiment with this? Any rough estimates what > > > > to expect? > > > > > > Thanks in advance, > > > > > > > > Regards, > > > > > > > > Robert > > > > _______________________________________________ > > > > sapdb.general mailing list > > > > [EMAIL PROTECTED] > > > > http://listserv.sap.com/mailman/listinfo/sapdb.general _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
