logo标签列表

VBA：用Excel查询Access，为什么这么慢？

excelvbaadojet
7
我在网上找到了一段用于查询 Access 并将数据插入 Excel（2003）的代码，但它比预期的慢很多：
Sub DataPull(SQLQuery, CellPaste)
Dim Con As New ADODB.Connection
Dim RST As New ADODB.Recordset
Dim DBlocation As String, DBName As String
Dim ContractingQuery As String

If SQLQuery = "" Then

Else
    DBName = Range("DBName")
    If Right(DBName, 4) <> ".mdb" Then DBName = DBName + ".mdb"

    DBlocation = ActiveWorkbook.Path
    If Right(DBlocation, 1) <> "\" Then DBlocation = DBlocation + "\"

    Con.ConnectionString = DBlocation + DBName
    Con.Provider = "Microsoft.Jet.OLEDB.4.0"
    Con.Open

    Set RST = Con.Execute(SQLQuery)
    Range(CellPaste).CopyFromRecordset RST

    Con.Close
End If

End Sub

这段代码的问题在于运行时间非常长。如果我在Access中打开并运行查询，只需要大约1/10的时间。有办法可以加快速度吗？或者可能有任何原因导致这个查询需要这么长时间吗？我的所有查询都是简单的select查询，带有简单的where语句和无连接。即使是select * from [test] 查询所需要的时间也比应该多得多。
编辑：我应该指出那一行代码 Range(CellPaste).CopyFromRecordset RST 需要很长时间。
1
当以逐步模式运行时，哪一行代码需要更长时间？ - shahkalpesh
Range(CellPaste).CopyFromRecordset RST - Dan
你检索了多少条记录？ - Thorsten
嗯，这改变了一切。我认为你不可能比“CopyFromRecorset”更快了。另外，你应该认真考虑重命名这个线程并删除你帖子中所有无关的东西。在我看来，Access显然与问题无关。 - Ryan Shannon
10个回答
3

我不是专家，但我使用几乎完全相同的代码并取得了良好的结果。一个区别是我同时使用了Command对象和Connection对象。 在你的代码中，你只是使用了

Set RST = Con.Execute(SQLQuery)

I

Dim cmd As ADODB.Command
Set cmd.ActiveConnection = con
cmd.CommandText = SQLQuery
Set RST = cmd.Execute

我不知道这是否有帮助或为什么有帮助，但也许会有帮助？:-)

2
我认为你并没有进行类似的比较。
在Access中，当你查看查询的数据视图时，会发生以下情况：
  • 使用现有的打开连接（并保持打开）；
  • 仅填充记录集的前几行（并保持打开）；
  • 部分结果集显示在专门用于此任务并针对Access所采用的本地数据访问方法进行优化的网格中（可能直接使用Access数据库引擎DLL）。
在你的VBA代码中：
  • 打开新连接（然后稍后关闭和释放）；
  • 使用所有行完全填充记录集（然后稍后关闭和释放）；
  • 使用非本地数据访问组件将整个结果集读入Excel的通用UI中。
我认为最重要的一点是，在Access中，直到你请求它为止（通常是导航到结果集的最后一行），数据视图才会获取整个结果集。ADO将始终获取结果集中的所有行。
第二个最重要的问题是假设存在完整的结果集，则将获取的行读入UI元素所需的时间以及Excel不适合该工作。
打开、关闭和释放连接和记录集应该是微不足道的，但仍然是一个因素。
我认为你需要对每个步骤进行计时，以找出瓶颈。在与Access进行比较时，请确保获取完整的结果集，例如检查返回的行数。
1

我建议您显式地创建Recordset，而不是使用Execute方法隐式地创建。在显式创建时，您可以设置其CursorType和LockType属性，这对性能有影响。

从我所看到的情况来看，您正在加载Excel中的数据，然后关闭记录集。您不需要更新、计算记录等等...因此，我的建议是使用CursorType = adOpenForwardOnly & LockType = adLockReadOnly创建一个Recordset：

...
RST.Open SQLQuery, Con, adOpenForwardOnly, adLockReadOnly
Range(CellPaste).CopyFromRecordset RST
...

记录集对象 (ADO)

我尝试了一下，它可以工作，但并没有导致运行时间的差异。无论如何，还是谢谢。 - Dan
1
这是有问题的，因为并非每个人都启用了DAO，所以将此文件传递给不同的人会更加困难。 - Dan
1
同样的情况也适用于ADO。我的理解是DAO是Access 2003使用的Jet引擎的一部分或者与之密切相关。因此，它很可能会随Access一起出现。但您始终可以检查以下位置：C：\ Program Files \ Common Files \ Microsoft Shared \ DAO \ dao360.dll（假设DAO 3.6）您还需要将代码转换为后期绑定，并仅在文件存在时使用DAO代码。 - JimmyPena
原帖中说：“在那里的查询大约只需要1/10的时间”。你真的认为切换到DAO会带来1000%的性能提升吗？ - onedaywhen
不会，但是所有条件相同的情况下它会更快。 - JimmyPena
@JP：也许如此（或者可能没有显著的增加），但这会分散我们对问题的注意力，而不是解决问题的方案。 - onedaywhen
显示剩余2条评论
1

我使用了你的代码，在不到7秒钟的时间内拉取了一个包含38列和63780行的表格 - 这大约是我所期望的 - 而较小的记录集则几乎瞬间完成。

你有这样的性能体验吗？如果是的话，那么这与我从Excel到MDB后端的ADO连接所期望的一致。

如果你看到的性能比这慢得多，那么肯定有一些本地环境条件影响了事情。

1

很多公式可能会引用查询。在宏中暂时打开手动计算，在所有查询都更新完成后再关闭。

这应该可以加快速度，但仍无法解决根本问题。

0

我不知道这是否有帮助，但我正在使用VBA和ADO连接到Excel电子表格。

它以闪电般的速度检索记录（＜5秒），但突然间变得非常缓慢（检索一个记录需要15秒）。这就是我看到你的帖子的原因。

我意识到我不小心把Excel文件打开了（我一直在编辑它）。

一旦我关闭它，所有东西都变得非常快。

0

问题十有八九与您使用的游标类型/位置有关。

在网络连接上使用动态游标可能会减慢数据检索速度，即使查询执行非常快。

如果您想快速获取大量数据，则需要在连接上使用CursorLocation = adUseClient。这意味着您只有一个静态本地游标，因此您将不会从其他用户那里获得实时更新。

然而，如果您只是读取数据，则可以避免ADO为每个单独的记录返回DB以检查更改。

最近我进行了更改，因为我有一个简单的循环，填充列表项，每个循环大约需要0.3秒。虽然不是太慢，但即使在1,000条记录上也需要30秒！仅更改游标位置就可以让整个过程在不到1秒钟内完成。

0
如果您检索了大量记录，这就可以解释为什么Range(CellPaste)需要很长时间。 （如果您在Access中执行查询，它不会检索所有记录，但如果使用CopyFromRecordset，则需要所有记录。）
CopyFromRecordset有一个MaxRows参数：
Public Function CopyFromRecordset ( _
    Data As Object, _
    <OptionalAttribute> MaxRows As Object, _
    <OptionalAttribute> MaxColumns As Object _
) As Integer

尝试将其设置为较低的值（如10或更低）是否会改变性能。

0

以下是一些可能的改进方案：

  1. 打开记录集后，将其保存为 XML 文件（rst.saveToFile xxx），然后让 Excel 重新打开它。
  2. 打开记录集后，将记录集数据放入数组中（rst.getRows xxx），并将该数组复制到活动工作表上。
  3. 并且，在任何时候都要尽量减少内存/访问需求：以只读、仅向前的方式打开记录集，在数据传输完成后关闭连接等。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接