代码之家 › 专栏 › 技术社区 › Heinzi

为什么打开第二个ADO客户端记录集的速度如此之慢?

ado ms-access vba sql-server

Heinzi · 技术社区 · 6 年前

重振

创建包含许多行的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;

创建新的VBA项目(如Access或Excel 2016),并添加对“Microsoft ActiveX数据对象2.8(或6.1)库”的引用。

修改以下重新编程代码以包含到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,我可以看到“慢速”和“快速”场景有所不同。

这就是“fast”查询的外观:

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本机客户端11.0,
- 最新的 ODBC Driver 17 for SQL Server .
问题可以不使用SQL Server OLE DB驱动程序复制。我们使用odbc而不是ole db,因为 OLE DB driver was deprecated 。我知道一段时间以前它是不被贬低的,但是我们目前不打算迁移我们的DAL。
启用mars( MARS_Connection=yes )没什么区别。SQLServer事件探查器显示两个记录集都使用相同的连接。这是 not that problem .
我们使用ADO而不是ADO.NET,因为MS Access还没有对.NET代码的内置支持。
我们不想使用服务器端游标。他们 are evil and have their own sets of problems 。我们刚从他们那里迁走。

0 回复 | 直到 6 年前