locked
What is the best way to open/maintain connection to database in loop? RRS feed

  • Question

  • User417741264 posted

    I am looping through each row from excel and validating the row data against database.

    My question is do we need to open and close connection for every row or open connection validate all rows and close the connection?

    In my research i noticed people recommending both approaches. Suggest me which is the best and why  

    Wednesday, May 31, 2017 10:55 AM

Answers

  • User-1509636757 posted

    It really depends on your what you are focused on. I would suggest to open a single connection, open the connection and write a loop with a single connection and then close once done. But, my answer would be different if every loop count may have lengthy process/execution, then I would suggest to have connection inside the loop. Another aspect, if you are looking for something like maintain Transaction (effect all or nothing in case of error) then you should opt single connection for loop. Ideal situation should be you open the connection as late as possible, and you dispose as soon as possible.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 31, 2017 11:05 AM
  • User753101303 posted

    Hi,

    Strictly speaking opening and closing the connection again an again should never be a problem with "connection pooling" that handles that for you behing the scene (the connection is not really closed but kept ready to be quickly reused and closed later if not reused for some time so opening/closing connections is almost "free").

    If you have an easy way to open the connection before the loop and close after the loop, you could try to see if it makes a difference. I don't expect to see a significant difference here.

    Keeping the connection opened for too long is never good (and sometimes even worse you see one that tries to explicitely keep a connection open for each single user when not mistakenly for all users).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 31, 2017 11:07 AM

All replies

  • User-1509636757 posted

    It really depends on your what you are focused on. I would suggest to open a single connection, open the connection and write a loop with a single connection and then close once done. But, my answer would be different if every loop count may have lengthy process/execution, then I would suggest to have connection inside the loop. Another aspect, if you are looking for something like maintain Transaction (effect all or nothing in case of error) then you should opt single connection for loop. Ideal situation should be you open the connection as late as possible, and you dispose as soon as possible.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 31, 2017 11:05 AM
  • User753101303 posted

    Hi,

    Strictly speaking opening and closing the connection again an again should never be a problem with "connection pooling" that handles that for you behing the scene (the connection is not really closed but kept ready to be quickly reused and closed later if not reused for some time so opening/closing connections is almost "free").

    If you have an easy way to open the connection before the loop and close after the loop, you could try to see if it makes a difference. I don't expect to see a significant difference here.

    Keeping the connection opened for too long is never good (and sometimes even worse you see one that tries to explicitely keep a connection open for each single user when not mistakenly for all users).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 31, 2017 11:07 AM