Open connections only when you need them?? 1

[Brando32]

I am fairly new to VB-SQL Server programming.

Exactly how resource intensive is opening and closing a connection VERSUS keeping connections open longer in order to do several recordset-related tasks before closing the connection.

I ask because I know in general tech-books say to minimize the connections you have open on the server at any given time. Any usually code examples open and close the connection within a single routine. Does this mean that opening and closing connections is considered low overhead and you should do it at the lowest level possible in your code?

Thanks

Brandon

 

[tlbroadbent]

I recommend keeping the connection open until all related queries complete and the data is displayed for the user. Close the connections while the user works on the screen. Repoen when the user submits new queries or updates.

The overhead associated with opening and closing connections can be significant but is reduced by the fact that SQL Server maintains the connection for a few minutes, even when the VB program closes the connection. When the VB program terminates, then the connection will be closed by SQL Server. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.