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”)
cnPubs.ConnectionString = strConnectString
cnPubs.ConnectionTimeout = i ‘here’s the clincher
cnPubs.Open
cnPubs.Execute strSQL
‘Set cnPubs = Server.CreateObject(“ADODB.Connection”)
Response.Write “ ” ‘ our simple delay tactic
cnPubs.Close
Set cnPubs = Nothing
Response.Flush
Next ‘ i
Response.Write “<p>Done”
</script>
Question 5: True or False: Opening multiple recordsets on the same connection object will insure you take a connection from the pool.
False.
While this is generally true, opening n forward-only, server-side, recordsets will, in fact, spawn n connections. By design, SQL Server can only open one forward-only cursor per connection at one time.
So how can we get around this limitation? You could use a different cursor type, use a client-side cursor, or close the recordset before opening another. In practice, however, it is often the case that the overhead of creating another connection pales in comparison to any of the solutions just mentioned. Unless you know you are having connection issues, this is best left alone.
The graph below shows the results of opening 1000 forward-only, server-side recordsets against a single connection.
For sample code and additional information on this topic, please see Microsoft Knowledge Base article Q 235282.
Conclusion
Connection pooling is done smartly behind the scenes by ADO, but use them to your maximum advantage, you need to be aware of several eye-opening gotchas. As we’ve seen above be particularly diligent in your use of ConnectionTimeout settings and your forward-only, server-side (“firehose”) cursors. Both can greatly affect your SQL Server performance.
References
http://www.sql-server-performance.com/bl_asp_ado.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;235282
Biography
Shawn Kresal is an award-winning web specialist, database developer, and raconteur in San Francisco, California. He revels in the minutia of both silicon-based and carbon-based interactions.
Copyright ©2003 Shawn Kresal. All Rights Reserved
]]>