SQL Server Performance Forum – Threads Archive
SQLOLEDB, SQLNCLI connection pooling issue
We have some VB6 code accessing MS SQL Server with ADODB using the SQLOLEDB provider which gives Timeout Expired errors when a large number of executes are performed against the same ADODB connection and when there is a recordset open on the same connection. The application opens a single ADODB connection at startup and closes it on shut down. I have determined the timeout error is based on the ConnectionTimeout value (set it low and it happens more quickly/often). I tried using one ADODB connection for all cursors and another ADODB connection for all executes. This solved the timeouts however we use begintrans/open a recordset/run an execute based on the recordset contents/committrans sequence in our code so that doesn’t work. I discovered the MS articles 258697 and 271128 which suggest that the problem is when a firehose cursor is used. Yes, our default cursor was a firehose, so I tried all combinations of the CursorType and LockType properties when opening the recordsets but no combination stopped the subsequent executes from using the same explicit connection. I then discovered the MS article 907264 which suggests using the new (with SQL Server 2005) SQLNCLI provider. This provider supports multiple active result sets and so its behaviour is supposedly different firehose cursors. Initial testing with this provider suggested it did indeed solve the problem but performing a much larger number of executes caused varying failures. All recovered on retry but??? The errors seen were ‘Named Pipes Provider: No process is on the other end of the pipe.’ and ‘Failed to read prelogin response.’. Named pipes are turned off at the server and weren’t being used by the client app. It was almost as if the SQL Server had run out of TCP ports and so decided to fall back to named pipes. This was certainly a better result than that we were getting with SQLOLEDB but still not bulletproof. This got me thinking about what the performance hit would be opening all these implicit connections and I discovered it was significant. I set the ConnectionTimeout up high enough to stop failures and performed some informal timings. SQLOLEDB with a recordset open took 45 seconds, close the recordset and the same job took 10 seconds. SQLNCLI with a recordset open took 25 seconds, close the recordset and it took 10 seconds. OK so SQLNCLI is more reliable than SQLOLEDB and is faster as well but it still isn’t bulletproof and there is a definite performance hit. Anyone got any idea how I can get the benefits of connection pooling while still having a recordset open?just my 2ct..
Do you use the ado methode begintrans or do you use t-sql begin trans ?
Scope of impact is different.
the ado.begintrans switches your connection to use implicit transactions whereas having a begin trans and commit/rollback in a sp or in a sqlcmd will only have impact on that command.
We use the ADO begin/commit. The sequence is like this.. Connection.BeginTrans
Connection.Open
RecordsetOpen
Connection.Excute
Connection.BaginTrans Each Connection.Execute creates a new implicit connection. Does this even if there is no begin/commit and the type of cursor (I’ve tried them all) we use for the recordset doesn’t change that behaviour.
]]>