SQL Server Connection Pooling Myths


Option Explicit

‘ ADO Connection Constants: Cursor location

Const adUseServer = 2 ‘ Uses a server-side cursor, DEFAULT

Const adUseClient = 3 ‘ Uses a client-side cursor

‘ ADO Connection Constants: Mode

Const adModeUnknown   = 0 ‘ Permissions cannot be set or determined

Const adModeRead      = 1 ‘ Read-only

Const adModeWrite     = 2 ‘ Write-only

Const adModeReadWrite = 3 ‘ Read and write permissions

Const strConnectString = “Provider=sqloledb;User ID=pubsLogin; pwd=pubsPassword; Data Source= YourServerName; Initial Catalog=pubs”

Const strSQL = “SELECT * FROM Authors WHERE au_lname=’This is SQL Statement 1′”

Dim cnPubs ‘ As New ADODB.Connection

Dim i

For i = 1 To 1000

    Response.Write “<br>Iteration ” & i

    Set cnPubs = Server.CreateObject(“ADODB.Connection”)

    Response.Write “<br>–cnPubs.CursorLocation=” & cnPubs.CursorLocation

    Response.Write “<br>–cnPubs.Mode=” & cnPubs.Mode

    cnPubs.ConnectionString = strConnectString

    cnPubs.CursorLocation = adUseClient

    cnPubs.Mode = adModeReadWrite


    cnPubs.Execute strSQL

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


    Set cnPubs = Nothing


Next ‘

Response.Write “<p>Done”


Question 4: True or False: Modifying the ConnectionTimeout will cause the pooled connection to remember this new value and could introduce problems when it goes back into the connection pool.


The ConnectionTimeout property cannot be reset on a pooled connection. So what happens if the credentials are the same as a pooled connection, but only the timeout is different? Modifying the ConnectionTimeout on a connection — with all other parameters being identical — will create a new connection. If you have a pooled connection with the same ConnectionTimeout value, and the same credentials, that connection will be preferred. In the code below each connection and its SQL statement are identical. The iteration value of i provides the unique Connection.Timeout value. Performance monitor shows all the proof we need that, all things being equal with the exception of the Timeout value, a new connection is created each and every time.


Try it for yourself.  


Option Explicit


Leave a comment

Your email address will not be published.