Multiple inserts

[sreenivas <ko...@rediffmail.com>]

  
Hi All,

I have 3 different "insert" statements for inserting data into different tables and mapped them to with parameter class too.

Now commit the transaction when all 3 inserts are successful, else rollback the transaction even if one of them fails.

As per requirement, these 3 insert statements need to be executed multiple times depending on number of records(in for /while)

Having said that I would like to know ideal mechanism to improve performance & reducing insertion time when there are more than 10000 records to be inserted in this way.

Thanks and regards,
Srini

Re: Multiple inserts

[Larry Meadors <la...@gmail.com>]

At forst glance, I'd consider a stored procedure to do the 3 inserts, and 
using a batch to execute the loop.

I.e., something like this:

start batch
start loop
call stored proc
end loop
end batch

Are the 10000 rows that you will be inserting predicatable? By that, I mean 
are the information that could be done entirely by the stored procedure, or 
is this some sort of data import? If they are predictable, I'd lean more 
towards just using a stored procedure to do the entire chore. If not, you 
might actually want to look at what is available for importing data into 
your database natively.

iBATIS is a cool tool, but not for every job. ;-)

Larry

On 1 Aug 2005 14:47:17 -0000, sreenivas <ko...@rediffmail.com> wrote:
> 
> 
> Hi All,
> 
> I have 3 different "insert" statements for inserting data into different 
> tables and mapped them to with parameter class too.
> 
> Now commit the transaction when all 3 inserts are successful, else 
> rollback the transaction even if one of them fails.
> 
> As per requirement, these 3 insert statements need to be executed multiple 
> times depending on number of records(in for /while)
> 
> Having said that I would like to know ideal mechanism to improve 
> performance & reducing insertion time when there are more than 10000 records 
> to be inserted in this way.
> 
> Thanks and regards,
> Srini 
> 
> 
>  <http://clients.rediff.com/signature/track_sig.asp>