Home » RDBMS Server » Performance Tuning » Bulk insert (Oracle 9i,windows)
Bulk insert [message #353289] Mon, 13 October 2008 04:46 Go to next message
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
Re: Bulk insert [message #353296 is a reply to message #353289] Mon, 13 October 2008 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Bulk insert [message #353301 is a reply to message #353296] Mon, 13 October 2008 05:09 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Bulk insert [message #353321 is a reply to message #353306] Mon, 13 October 2008 05:36 Go to previous message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

Thanks for your information
Previous Topic: about sql access advisor
Next Topic: TRIGGERS/PROCEDURES running too slow after index rebuild
Goto Forum:
  


Current Time: Wed Dec 07 22:06:42 CST 2016

Total time taken to generate the page: 0.12213 seconds