Re: Opening / Closing SQL Server connections

[Patrice]

This is also currently what I do (though the connection is private to the
the layer).

I'm looking at this thread as I've done some investigation about this once.

When connection pooling is enabled the time for opening/closing a connection
is really very short. I considered then to open/close the connection on a
per call basis.

The benefit I see is that you don't have to include in your debug release a
check to see if all connections are properly closed. Also it allows to close
the connection generally earlier in the page (or at least as soon as
possible) and you could even imagine in some cases a lengthy thing that
don't need the DB and another page could use a connection in between.

IMO definitely worth to consider. I would like to ear from someone that
already open/close on each call...

TIA

Patrice

--

 

[Guest]

If you understand the ADO.NET internals, it is better to open and close for
each call. However, you can also work smart by grabbing multiple info sets
(DataTables from SELECT statements, for example) on one pass (single stored
procedure).

When you hold a connection, on a busy app esp., you can end up using tons
more memory, increase licensing costs and even slow down the apps, as you
force new connections instead of using the pool effectively. While saving a
microsecond for each process, you rob ADO.NET of its highly efficient pooling
mechanism and implement your own connection control process.

There are times where holding a connection is wiser, but I would shy away
from this methodology, as a rule.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Angel Saenz-Badillos[MS]]

I am definitely biased in this but if you want my opinion I am a huge fan of
opening a connection per call. There are only three things you need to do:

Use the "using" or a try .. finally connection.Dispose() construct to
_guarantee_ that your connection is disposed.
Open the connection as late as possible. //(if the next statement is not an
Execute why do you need an open connection?)
Close the connection as early as possible.//(don't place anything between
open and close that could be done after close)

This not only results in very easy to read and maintain code, it will
guarantee very close to the best performance while giving your application
the room to become scalable. If you use multiple threads in your application
this becomes even more critical since ado.net objects are not thread safe.

The only exception I can think of would be a Winforms single threaded app
where you want to control your connections manually.
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/

 

[Michael D. Long]

increase licensing costs

Has there been a change in the SQL Server license that allows middleware to
multiplex a CAL? If not, how does connection pooling decrease licensing
costs?