pros and Cons behind opening and closing lot of database sessions while doing bulk updates/inserts

Copper Contributor

Hi All,

 

We have a requirement where we have bulk amount of files to be moved between folders and update database on each file status. As part of this requirement we had couple of discussion within our team and have one question on the approach.

 

Approach1:

when a file is moved successfully ,collect file_id into list and once all files are moved update table with file_id's collected in list. This needs only one database session to open and close.

 

Approach 2:

When each file is moved successfully -->open database session-->update table with file status --> close the session. Here we will be opening many sessions and closing them immedietly.

 

So my question is if we go with approach 2 what are the issues when we are opening session and closing it immediately? We are opening many sessions here we are talking about 100k - 

200k files at once. Will there be any issue with database performance?

 

Thanks in advance

0 Replies