List:MaxDB« Previous MessageNext Message »
From:Alexander Schroeder Date:April 9 2003 3:33pm
Subject:RE: determining optimal jdbc batch size for a given table
View as plain text  
Hello Wojtek,

the 'optimal size' depends on a lot of conditions, and only one of them is 
the number of elements in the batch:

- the amount of memory wasted in the client (all data bound for batch is kept
  until execution, and this might stress the client a bit)

- the amount of batch items executed in one transaction (as these batch statemenst
  impose usually some row locks, a lot of locks may build up, having some side 
  effects)

- neither stored procedure calls nor statemenst that take no parameters 
  (as prepared statements) are good candidates for batched execution anyway.

For the curious, who want to estimate how much data will fit in one packet,
there are the following things to look at:

- the packet size (parameter _PACKET_SIZE)

- the amount of data needed for one inserted updated row
 (approximate but different to
   http://www.sapdb.org/7.4/htmhelp/ff/baaba8d55a11d2a97400a0c9449261/content.htm)

  Data Type            Size
  ----------------------------------------
  FIXED(p,s)           (p+1) DIV 2 + 2
  FLOAT(p)             (p+1) DIV 2 + 2
  BOOLEAN              2
  DATE                 11 (21 if UNICODE database)
  TIME                 9 (17 if UNICODE database)
  TIMESTAMP            27 (53 if UNICODE database)  
  LONG                 41 + data (2 bytes per char if LONG UNICODE)
  (VAR)CHAR(n)         n + 1   
  (VAR)CHAR(n) UNICODE n*2 + 1

Now, subtract 4K from the _PACKET_SIZE, subtract approx. 150 bytes from _PACKET_SIZE
for some management information, and divide the rest by your computed row size, and 
you get an estimate how many rows will fit in one packet.

For LONG data you have of course keep in mind, that for real long LONG data follow-up
packets are sent, if the size does not fit (and then the result of your calculation is
1 row).

Regards
Alexander Schröder
SAP DB, SAP Labs Berlin

> -----Original Message-----
> From: wojtek@stripped [mailto:wojtek@stripped]
> Sent: Wednesday, April 09, 2003 3:54 PM
> To: sapdb.general@stripped
> Subject: determining optimal jdbc batch size for a given table
> 
> 
> Hello,
> 
> I'm inserting a lot of records into SAPDB via JDBC.
> 
> More / less like this:
> 
> String sql = "INSERT INTO my_table VALUES(?)";
> PreparedStatement pstmt = connection.prepareStatement(sql);
> 
> // Insert rows of data
> for (int i=0; i< batchSize; i++) {
>    pstmt.setString(1, ""+i);
>    pstmt.addBatch();
> }
> 
> // Execute the batch
> pstmt.executeBatch();
> 
> The performance observed varies substantially depending on 
> the batch size.
> 
> Is there a way to determine the optimal batch size for a given table?
> 
> Or maybe a way to tell wether I should keep on adding 
> statements to the
> current batch?
> 
> Is this related to some internal buffer size? Should I increase it
> (somehow)?
> 
> 
> Thanks,
> Wojtek Narczynski
> 
> 
> _______________________________________________
> sapdb.general mailing list
> sapdb.general@stripped
> http://listserv.sap.com/mailman/listinfo/sapdb.general
> 

Thread
determining optimal jdbc batch size for a given tablewojtek9 Apr
RE: determining optimal jdbc batch size for a given tableAlexander Schroeder9 Apr