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

Question

复制品

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 数据对象 2.8（或 6.1）库”的引用。

3. 修改以下重现代码以包含到 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
   

---

问题

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

---

更多细节

使用 SQL Server Profiler，我可以看到“慢”和“快”方案不同。

这就是“快速”查询的样子：

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可以返回与请求的游标不同类型的游标。在这种情况下，检查CursorType和CursorLocation之后rs2.Open显示在两种情况下（慢速和快速）都返回了客户端静态游标。

• 我已经测试了以下 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 代码的内置支持。

• 我们不想使用服务器端游标。他们是邪恶的，并且有自己的一系列问题。我们刚刚离开他们。

标签： sql-servervbams-accessado