[thelist] DBCon to SQL good/bad practice

Ken Schaefer ken at adOpenStatic.com
Mon Nov 24 19:59:23 CST 2003


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "John Dobson" <j.dobson at find-a-part.com>
Subject: [thelist] DBCon to SQL good/bad practice


: If you are making DB connections to a database such as
: SQL, should you close any connection you open?  Is it
: bad practise to leave them open?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The answer to the questions you have depends on your environment, and what
you're trying to do.

A couple of assumptions that I'm making:
a) you are talking about SQL Server (not any other relational database)
b) you are talking about some kind of web-based environment (not some kind
of rich client environment where the client application may need a
persistant connection to the DB server)

In such a scenario, 99.99% of the time you do not want to keep connections
open any longer than is absolutely required. Any DB server can't
realistically maintain any more than a few hundred (say 500) concurrent,
physical connections. To scale the application (ie to have more than 500
clients), each client must only use their connection for as little time as
possible.

Every major data access technology out there implements some kind of
connection pooling [1]. With connection pooling the lower level data access
components maintain a persistant pool of connections to the database server.
When your upper layer (eg Cold Fusion page, ASP page) needs a connection, it
is retrieved from the pool, and handed to the page. You then need to return
this connection back to the pool as soon as possible so that other pages can
use it. [2] In the ASP/ADO world this means that you *don't* want to
implicitly create ADO connection objects since you can't easily return them
to the OLEDB Provider Pool.

In the ASP world, to minimise the amount of time you have a connection open,
look at GetRows() and GetString(), which are methods you are call on the
recordset object, that moves the data into local variables (either an array,
or a string), allowing you to dispose of the recordset immediately (and also
meaning you can use the very fast forwardOnly readOnly cursors), and hence
the connection. It'll also mean you have neater code, as you can do this:

<%
Open Connection
Get Recordset1
Put Recordset1 into array
Close Connection
%>
<html>
    Your HTML is here
</html>

Cheers
Ken

[1] For MDAC (ADO, OLEDB etc), you can read this article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp

[2] INFO: Connection Pool Management by ADO Objects Called From ASP
http://support.microsoft.com/?id=191572



More information about the thelist mailing list