SQL Server Connection Pooling Myths

Question 2: True or False: Setting a connection to Nothing is important for proper pooling.


Closing the connection is critical to pooling, but destroying the object by setting it to Nothing is only important to resources on your web server. That makes performing this step important, but for a reason separate from pooling. It has no bearing whatsoever on connection pooling. Using the code below we see that leaving out the destruction of the object yields no different results with respect to the open number of connections.


Option Explicit

Const intConCount = 50

Dim conArray ‘ array of connection objects

Dim i

ReDim conArray(intConCount)

For i = 1 To intConCount

    Response.Write “Processing connection #” & i & “<br>”

    Set conArray(i) = Server.CreateObject(“ADODB.Connection”)

    conArray(i).ConnectionString = strConnectString1


    conArray(i).Execute strSQL2


    Response.Write “&nbsp;” ‘ our simple delay tactic

    ‘Set conArray(i) = Nothing ‘ un/comment this line to see that there
    ‘is no difference


Next ‘i

Response.Write “<p>Done”


Question 3: True or False: Changing the CursorLocation (adUseClient to adUseServer for example) or Mode (adModeRead, adModeShareDenyRead, etc.) of a pooled connection may cause problems when it is returned to the pool.


Wouldn’t it be appalling if another piece of code set the Connection.CursorLocation (or the Mode or Attributes) to something incompatible, and your code reused the pooled connection with the wrong settings?! Fortunately, this cannot happen. When a connection is plucked from the pool only, the credentials are reused. All properties are reset to the connection’s default values. Well, almost all of them, but that’s getting ahead of ourselves.

Executing the code below, we can see that opening 1000 connections will always result in the CursorLocation defaulting to adUseClient, and the Mode defaulting to adModeUnknown, even though every connection after the initial two is originating from the pool.

All output to the web browser will resemble the following snippet:

Connection 1, iteration 42



This clearly demonstrates that the pool returns a connection with a CursorLocation of adUseServer (2) and a Mode of adModeUnknown, regardless of what it was set to previously.


Leave a comment

Your email address will not be published.