Optimum no.of records for batchInsert

, ,
1

Hello All,

I have a requirement for inserting some 70000 records (6 to 10 fields) into MS SQL server tables. As you know I have the option of batch insert for this, I would like to know the below from some of you who already used this batch JDBC jobs for huge volumes of data.

  1. Did you notice any optimum no.of records for a batch insert job to have good performace.
  2. Did you face any specific performance issues or bottlenecks in the process?

Thanks & Cheers:)
Gunnasekkhaar

2

Hi,

I have not been able to find any optimum number for batch insert, but I am successfully able to insert around 40 K of records(30 fields with varchar and date type) in less than half a minute in oracle 10g. This is on IS 6.5 with min and max memory of 2048 MB.

My exprience says 70 K records with (6 to 10 fields) shouldnt be any bottleneck provided you have good memory allocated. Also you will have to make a different calculation if at all you are trying to insert BLOB and CLOB type fields.

Thanks.
Jiten

3

Hi Jiten,

Helpful testimonial. We don’t have any BLOB or CLOB fields, so I could go on.
Any other testimonials friends?

Thanks
Gunnasekkhaar