Why is opening a second ADO client-side recordset so terribly slow?

Question

Repro

1. Create an SQL Server table with a lot of rows:

   CREATE TABLE largetable (field int);
   
   INSERT INTO largetable (field)
   SELECT TOP 10000 ROW_NUMBER() OVER(ORDER BY t1.number) 
     FROM master..spt_values t1 CROSS JOIN master..spt_values;
   

2. Create a new VBA project (e.g. Access or Excel 2016) and add a reference to "Microsoft ActiveX Data Objects 2.8 (or 6.1) Library".

3. Modify the following repro code to include the correct connection string to your SQL Server database. Then execute it in your VBA module:

   Public Sub Repro()
       Dim cn As New ADODB.Connection
       Dim r1 As New ADODB.Recordset
   
       cn.ConnectionString = "Driver={SQL Server Native Client 11.0};Server=...;Database=...;Trusted_Connection=yes"
       cn.Open
   
       ReadLargeTable cn       ' Fast (0.01-0.03s)
   
       r1.CursorLocation = adUseClient
       r1.Open "SELECT 1", cn, adOpenStatic
   
       ReadLargeTable cn       ' Slow (6-10s)
   
       r1.Close
   
       ReadLargeTable cn       ' Slow (6-10s)
   
       Set r1 = Nothing
   
       ReadLargeTable cn       ' Fast (0.01-0.03s)
   
       cn.Close
   End Sub
   
   Private Sub ReadLargeTable(ByVal cn As ADODB.Connection)
       Dim d As Double
       Dim r2 As New ADODB.Recordset
   
       d = Timer
       r2.CursorLocation = adUseClient
       r2.Open "SELECT field FROM largetable", cn, adOpenStatic
       Debug.Print Timer - d
   
       r2.Close
       Set r2 = Nothing
   End Sub
   

---

Question

As you can see, opening a second client-side cursor is painfully slow if another one is already open. I would like to know why this happens and what I can do about it.

---

More details

Using SQL Server Profiler, I can see that the "slow" and the "fast" scenarios differ.

This is what a "fast" query looks like:

SQL:BatchStarting   SELECT field FROM largetable
SQL:StmtStarting    SELECT field FROM largetable
SQL:StmtCompleted   SELECT field FROM largetable
SQL:BatchCompleted  SELECT field FROM largetable

This is what a "slow" query looks like:

RPC:Starting
  declare @p1 int
  set @p1=0
  declare @p3 int
  set @p3=16388
  declare @p4 int
  set @p4=8193
  declare @p5 int
  set @p5=0
  exec sp_cursoropen @p1 output,N'SELECT field FROM largetable',@p3 output,@p4 output,@p5 output
  select @p1, @p3, @p4, @p5

RPC:Completed
  ...same SQL as above...

RPC:Starting    exec sp_cursorfetch 180150003,2,0,1
RPC:Completed   exec sp_cursorfetch 180150003,2,0,1
RPC:Starting    exec sp_cursorfetch 180150003,2,0,1
RPC:Completed   exec sp_cursorfetch 180150003,2,0,1
RPC:Starting    exec sp_cursorfetch 180150003,2,0,1
RPC:Completed   exec sp_cursorfetch 180150003,2,0,1
...repeat 10000 times...

So it appears that when the query is fast, all the data is loaded in one batch, whereas when the query is slow, each record is transmitted individually.

Obviously, I'd like to force ADO to always use the "fast" route, even if another client-side cursor is already open.

---

Additional notes

• I am aware that Recordset.Open can return a different type of cursor than the one requested. In this case, checking CursorType and CursorLocation after rs2.Open reveals that in both cases (slow and fast) a client-side static cursor is returned.

• I have tested the following SQL Server ODBC drivers, and the problem can be reproduced with all of them:

  • the "classic" MDAC {SQL Server} ODBC driver,
  • SQL Server Native Client 11.0,
  • the most up-to-date ODBC Driver 17 for SQL Server.

• The problem can not be reproduced with the SQL Server OLE DB driver. We use ODBC instead of OLE DB, because the OLE DB driver was deprecated. I am aware that it was un-deprecated some time ago, but we currently do not plan to migrate our DAL.

• Enabling MARS (MARS_Connection=yes) does not make a difference. SQL Server Profiler shows that both recordsets use the same connection. This is not that problem.

• We use ADO instead of ADO.NET because MS Access does not have built-in support for .NET code yet.

• We don't want to use server-side cursors. They are evil and have their own sets of problems. We just finished migrating away from them.

Answer 1

If the recordset specifies adUseClient but the connection does not, a second adUseClient recordset makes everything horribly slow.

This showed up for me when users open two forms at once, each with an ADO RecordSource. With just one form open, everything is fine. With two forms open, forget it!

The solution is to have a second connection, with adUseClient. Any number of recordsets can use this connection without slowness. A form's Form_Load typically contains Set Me.Recordset = BackendQueryUseClient("SELECT * FROM MyTable").

I'm using SQL Server authentication. I clone the current connection and store the second connection in a Static, created on first use, so it can be used for subsequent requests without the overhead of opening a new connection for each request. I use the DiscardCache option to close the connection if needed.

Public Function BackendQueryUseClient(ByVal q As String) As adodb.Recordset
Dim rs As adodb.Recordset
Set rs = BackendConnectionUseClient.Execute(q)
Set BackendQueryUseClient = rs
End Function

Public Function BackendConnectionUseClient() As adodb.Connection
Static pConnection As adodb.Connection
If pConnection Is Nothing Then
  Dim cs As String, csNew As String
  cs = CurrentDb.TableDefs("Employees").Connect     ' choose any table that will be in the Backend DB
  Set pConnection = New adodb.Connection            ' existing connection string won't round-trip so must be rearranged
  pConnection.CursorLocation = adUseClient
  csNew = "ODBC;DATABASE=" & CSValue("DATABASE", cs) & _
          ";SERVER=" & CSValue("SERVER", cs) & _
          ";DRIVER=ODBC Driver 17 for SQL Server;" & _
          ";UID=" & CSValue("UID", cs) & _
          ";PWD=" & CSValue("PWD", cs)
  pConnection.Open csNew
  pConnection.Execute "USE [" & CSValue("DATABASE", cs) & "]"
End If
Set BackendConnectionUseClient = pConnection
End Function

Public Function CSValue(Keyword As String, ConnectionString As String) As String
CSValue = RegExpFirstMatch(Keyword & "=([^;]+)", ConnectionString)
End Function

' Return "" if no match found
Public Function RegExpFirstMatch(Pattern As String, SourceString As String) As String
Dim RegEx As RegExp, matches As MatchCollection
Set RegEx = New RegExp
RegEx.Pattern = Pattern
Set matches = RegEx.Execute(SourceString)
If matches.Count > 0 Then
    RegExpFirstMatch = matches.Item(0).SubMatches(0)
End If
End Function