OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Bulk insert (Oracle 9i,windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Bulk insert [message #353289]

Mon, 13 October 2008 04:46

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

Hi all,

Does commit after each insert statement (bulk insert) degrade database performance.If yes how?

Say for example ,i want to do bulk insert of 100 tables to development database from production database.So i have made commit after each insert statement.

Please give me your valuable suggestion.

Thanks & Regards,
Balaji

Report message to a moderator

Re: Bulk insert [message #353296 is a reply to message #353289]

Mon, 13 October 2008 04:56

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Does commit after each insert statement (bulk insert) degrade database performance.If yes how?

Yes, in the same way that other insert.

Commit ONLY when the business requirements say to end the transaction.

Regards
Michel

Report message to a moderator

Re: Bulk insert [message #353301 is a reply to message #353296]

Mon, 13 October 2008 05:09

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

first of all i want to say thanks for your quick response.

Say for example ,i am doing bulk insert of about 50,000 records and in the middle it got failed due to some external hardware issue.At this point only 20,000 records were inserted.how to solve the issue at this stage?

i cant keep rollback all (20,0000..)uncommited records everytime.

I think commit after each insert should be the better option for this issue.

Please correct me if i am wrong.

Regards,
Balaji

[Updated on: Mon, 13 October 2008 05:13]

Report message to a moderator

Re: Bulk insert [message #353306 is a reply to message #353301]

Mon, 13 October 2008 05:22

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

how to solve the issue at this stage?

Recover.

Quote:

cant keep rollback all (20,0000..)uncommited records everytime.

You must.

Quote:

I think commit after each insert should be the better option for this issue.

Best? No, the last if you can't do anything.
Commit is NOT a technical statement, it is a BUSINESS one.
You commit when the business says to do it.

Regards
Michel

Report message to a moderator

Re: Bulk insert [message #353321 is a reply to message #353306]

Mon, 13 October 2008 05:36

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

Thanks for your information

Report message to a moderator

Previous Topic:	about sql access advisor
Next Topic:	TRIGGERS/PROCEDURES running too slow after index rebuild

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 19:13:37 CST 2024