Open and close connection or leave open

[bluep651]

Is it more efficient for an enquiry program to open a connection and keep it open until the program ends, or open and close a connection each time the data needs refreshing (when the user requests it, so could be frequent, or not, as the case may be)?

SQL Server btw

TIA,

blue

 

[JuggaloBrotha]

I'm a fan of opening the connecting to get/save data then closing it.

 

[bluep651]

Thank you, I'll go with that

 

[cjard]

Microsoft recommend you open, do work, and close. Additionally, because of connection pooling done by the .NET framework, you arent actually opening and closing TCP connections like you might think you are; youre just leasing and returning connections from/to the pool. Keeping a connection "open" can only serve to screw this mechanism up.

In short, do what Microsoft say to do (unless you've deliberately turned off pooling, in which case O/C will have a horrendous performance hit)