we are inserting row one by one in loop which approach is good for this scenario insert_all vs multi insert?
Data size is 5 lac records and need to finish transaction within 20 seconds.
Is there anything to boost update performance?
Please suggest…
Thanks in advance!!
I don’t think I can specifically answer our question without more details for what your application is doing, but I can give some general advice.
- Inserting a row at a time in a loop is adequate for some apps but is not nearly the fastest way to do it.
- If you want the best speed for set-oriented inserts, user INSERT INTO… SELECT FROM… because it can run in parallel, fully compiled, with the least data movement and translation.
- if you are programming in a stored proc and are retrieving multiple rows into an array with COLLECT and just want to insert them to a target, INSERT_ALL may be good for that. But if you are trying to process >>1000 rows this way, probably it’s better to use INSERT INTO… SELECT FROM… instead.
What do you mean “data size is 5 lac records”?