Excel VBA中使用ADODB Recordset获取前置值查询过慢求助

我在Access中运行以下查询时速度很快、性能优异，但在Excel中通过ADODB.Recordset执行逻辑相同的查询（数据源为Excel工作表），使用Range.CopyFromRecordset复制结果耗时约15分钟。

Access查询语句：

SELECT 
    d.ShamsiDate,
    d.UnitNo,
    d.[Counter_MVH+],
   (SELECT max( d1.[Counter_MVH+] )                
    FROM tblData AS d1                     
    WHERE d1.ShamsiDate < d.ShamsiDate AND d1.UnitNo = d.UnitNo
    ) AS PriorValue
FROM tblData d;

Excel中使用的VBA代码：

Dim cn As Object, rs As Object, sq As String
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

sq = _
    "SELECT d.ShamsiDate, d.UnitNo, d.[Counter_MVH+], " & _
        "(SELECT MAX(d1.[Counter_MVH+]) " & _
        "FROM [Data$] d1 " & _
        "WHERE d1.UnitNo = d.UnitNo AND d1.ShamsiDate < d.ShamsiDate ) AS PriorValue " & _
        "FROM [Data$] d;"

cn.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0 Xml;HDR=YES';"
cn.Open
rs.Open sq, cn, 3, 1

Sheet2.Range("A2").CopyFromRecordset rs

rs.Close
cn.Close

核心问题分析 #

Access的查询性能优异是因为它会自动利用表索引优化嵌套子查询逻辑，但Excel作为数据源时，OLEDB驱动无法为工作表创建索引，原查询中的关联子查询会对每一行执行一次全表扫描，数据量较大时性能会呈指数级下降。

优化方案 #

1. 改用JOIN替代嵌套子查询 #

将关联子查询改为LEFT JOIN + GROUP BY的方式，大幅减少全表扫描次数：

sq = _
    "SELECT d.ShamsiDate, d.UnitNo, d.[Counter_MVH+], MAX(d1.[Counter_MVH+]) AS PriorValue " & _
    "FROM [Data$] d " & _
    "LEFT JOIN [Data$] d1 ON d.UnitNo = d1.UnitNo AND d1.ShamsiDate < d.ShamsiDate " & _
    "GROUP BY d.ShamsiDate, d.UnitNo, d.[Counter_MVH+] " & _
    "ORDER BY d.UnitNo, d.ShamsiDate;"

这种方式仅需两次全表扫描，而非原方案的N次（N为数据行数）。

2. 直接用VBA排序后计算PriorValue #

既然数据源是Excel，直接利用VBA处理排序后的数据，避免ADODB的低效查询：

Sub CalculatePriorValue()
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim lastRow As Long, i As Long
    Dim currentUnit As String, lastValue As Double
    
    Set wsSource = ThisWorkbook.Sheets("Data")
    Set wsDest = ThisWorkbook.Sheets("Sheet2")
    
    ' 按UnitNo和ShamsiDate排序数据源
    lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row
    wsSource.Range("A1:D" & lastRow).Sort Key1:=wsSource.Range("B1"), Order1:=xlAscending, _
        Key2:=wsSource.Range("A1"), Order2:=xlAscending, Header:=xlYes
    
    ' 复制基础数据到目标表
    wsSource.Range("A1:C" & lastRow).Copy wsDest.Range("A1")
    
    ' 计算PriorValue
    wsDest.Range("D1").Value = "PriorValue"
    currentUnit = wsDest.Range("B2").Value
    lastValue = 0 ' 初始值可根据业务调整
    
    For i = 2 To lastRow
        If wsDest.Range("B" & i).Value = currentUnit Then
            wsDest.Range("D" & i).Value = lastValue
            lastValue = wsDest.Range("C" & i).Value
        Else
            currentUnit = wsDest.Range("B" & i).Value
            lastValue = wsDest.Range("C" & i).Value
            wsDest.Range("D" & i).Value = 0 ' 新Unit的第一条记录无前置值，可调整
        End If
    Next i
End Sub

线性遍历排序后的数据集，性能远优于ADODB的嵌套查询。

3. 借助Access临时表优化查询 #

如果必须保留SQL逻辑，可将Excel数据导入Access临时表，利用Access的索引能力：

Sub UseAccessTempTable()
    Dim cn As Object, rs As Object
    Set cn = CreateObject("ADODB.Connection")
    
    ' 连接到临时Access数据库（需提前创建或使用内存数据库）
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\TempDB.accdb;"
    cn.Open
    
    ' 导入Excel数据到Access临时表
    cn.Execute "SELECT * INTO tblTemp FROM [Excel 12.0 Xml;HDR=YES;Database=" & ThisWorkbook.FullName & "].[Data$];"
    
    ' 创建索引优化查询
    cn.Execute "CREATE INDEX idx_UnitDate ON tblTemp (UnitNo, ShamsiDate);"
    
    ' 执行原查询逻辑
    Set rs = cn.Execute("SELECT d.ShamsiDate, d.UnitNo, d.[Counter_MVH+], " & _
        "(SELECT MAX(d1.[Counter_MVH+]) FROM tblTemp d1 WHERE d1.UnitNo = d.UnitNo AND d1.ShamsiDate < d.ShamsiDate) AS PriorValue " & _
        "FROM tblTemp d;")
    
    ' 复制结果到Excel
    Sheet2.Range("A2").CopyFromRecordset rs
    
    ' 清理临时表
    cn.Execute "DROP TABLE tblTemp;"
    
    rs.Close
    cn.Close
End Sub

利用Access的索引优化能力，让原查询逻辑保持高效。

4. 优化ADODB游标参数 #

调整Recordset的打开参数，使用向前只读游标减少资源消耗：

' 后期绑定方式
rs.Open sq, cn, 1, 1 ' adOpenForwardOnly = 1, adLockReadOnly = 1

原代码使用的3,1（动态游标）会增加内存开销和性能损耗，向前只读游标更适合一次性读取数据的场景。

内容的提问来源于stack exchange，提问作者LavanHezha