为什么打开第二个ADO客户端记录集如此缓慢

Question

Repro

1. 创建一个包含许多行的SQL Server表：

   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. 创建一个新的VBA项目(例如Access或Excel 2016(，并添加对"Microsoft ActiveX Data Objects 2.8(或6.1(Library"的引用。

3. 修改以下repro代码以包含到SQL Server数据库的正确连接字符串。然后在VBA模块中执行：

   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
   

---

问题

正如您所看到的，如果另一个客户端游标已经打开，那么打开第二个客户端游标的速度会非常慢。我想知道为什么会发生这种情况以及我能做些什么。

---

更多详细信息

使用SQLServerProfiler，我可以看到"慢速"one_answers"快速"场景有所不同。

这就是"快速"查询的样子：

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

这就是"慢速"查询的样子：

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...

因此，当查询速度快时，所有数据都会在一批中加载，而当查询速度慢时，每条记录都会单独传输。

显然，我想强制ADO始终使用"快速"路由，即使另一个客户端游标已经打开。

---

附加注释

• 我知道Recordset.Open可以返回与请求的游标不同类型的游标。在这种情况下，在rs2.Open之后检查CursorType和CursorLocation会发现，在这两种情况下(慢速和快速(都会返回客户端静态光标。

• 我测试了以下SQL Server ODBC驱动程序，所有这些驱动程序都可以重现问题：

  • "经典"MDAC{SQL Server}ODBC驱动程序
  • SQL Server Native Client 11.0
  • 适用于SQL Server的最新ODBC驱动程序17

• 使用SQL Server OLE DB驱动程序可以而不是重现该问题。我们使用ODBC而不是OLE DB，因为OLE DB驱动程序已被弃用。我知道它在一段时间前没有被否决，但我们目前不打算迁移我们的DAL。

• 启用MARS(MARS_Connection=yes(并没有什么区别。SQL Server Profiler显示两个记录集使用相同的连接。这不是那个问题。

• 我们使用ADO而不是ADO.NET，因为MS Access还没有对.NET代码的内置支持。

• 我们不想使用服务器端游标。他们是邪恶的，有自己的一系列问题。我们刚从他们那里迁移出去。

Answer 1

如果记录集指定了adUseClient，但连接没有，那么第二个adUseClient记录集会使一切变得非常缓慢。

当用户同时打开两个表单，每个表单都有一个ADO RecordSource时，我就看到了这一点。只要打开一张表格，一切都很好。打开两张表格，忘了它！

解决方案是使用adUseClient进行第二次连接。任何数量的记录集都可以使用此连接，速度不会太慢。表单的Form_Load通常包含Set Me.Recordset = BackendQueryUseClient("SELECT * FROM MyTable")。

我正在使用SQL Server身份验证。我克隆了当前连接，并将第二个连接存储在第一次使用时创建的静态中，这样它就可以用于后续请求，而无需为每个请求打开新连接。如果需要，我使用DiscardCache选项来关闭连接。

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