When should you close a database connection?

[SheSaidImaPregy]

What is better to do, and if you could, please include the pros and cons along with your opinions:

1) Open the connection to the database as late as possible and end it as early as possible, and do this a total of 5 times in one page_load.

2) Open the connection to the database as late as possible and KEEP it OPEN until the last SQL Query has been retreieved, then close it.

3) Split the connections; Keep one Connection open for 2 or 3 query executes and then close, then open another and execute the remaining.

I believe number 2 is the best, as if 100 people access the page at once, that would create 100 connections in the matter of seconds, rather than option one of creating 500 connections in the matter of seconds. But does leaving the connections open use up more memory and drain more performance than just having 500 connections? Or does it really matter? Let me know.

[Ancient Dragon]

Opening a database is expensive in terms of both time and resources. So the fewer times you have to open it the better performace your program will achieve. I would opt for option #2. Of course one problem is that you may need to tell the database server to flush the data to disk often. But a good database server will probably do that anyway. The programs I worked with each user had his/her own connection, for security reasons. The connection was opened when the user logged onto the application program and was not closed until the user logged off. There was no more than 8 hours between logon and logoff (one 8 hour shift). One problem we had to overcome was accidental dropped connections do to network down or server down.

[Infarction]

Option 2, closing after the last query for the page load (which may be automatic anyways, when everything is GC'ed). The overhead of opening and closing the connections for a single load will always be more than keeping an idle connection for processing 1 request. If you want to keep it around longer than that, there may be security ramifications, as AD suggested.

[SheSaidImaPregy]

Great, thought so. I've always done option 2 but had to make sure. Thank you!