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

VBA:用Excel查询访问。为什么这么慢?

jet ado excel-vba vba excel

Dan · 技术社区 · 15 年前

我在网上找到了这个代码,用来查询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的时间。有没有办法加快速度?或者这可能需要这么长的时间?我的所有查询都是简单的选择查询,带有简单的WHERE语句,没有连接。即使是“select*from[test]”查询也比它应该花费的时间长得多。

编辑:我应该指定“range(cellpaste).copyFromRecordset rst”这一行要花很长时间。

10 回复 | 直到 9 年前

Ryan Shannon 15 年前

我不是专家,但我运行的代码几乎完全相同,结果很好。一个区别是我使用 Command 对象以及 Connection 对象。你在哪里

Set RST = Con.Execute(SQLQuery)

我

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

我不知道这是否有帮助,也不知道为什么会有帮助,但也许会有帮助?-)

JimmyPena Alex K. 15 年前

因为您使用的是Access2003,所以使用DAO代替,使用喷气发动机会更快。

见 http://www.erlandsendata.no/english/index.php?d=envbadacexportdao 用于示例代码。

请注意,不要使用“as new”关键字,因为它会导致意外的结果。

manji 15 年前

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

据我所见,您正在Excel中加载数据,然后关闭记录集。您不需要更新、计数记录等…所以我的建议是 记录集 具有 CursorType = adOpenForwardOnly & LockType = adLockReadOnly :

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

Recordset Object (ADO)

Lunatik 15 年前

我用了你的代码,在不到7秒钟的时间内,把一张由38列和63780行组成的表格拉了进来——这大概是我所期望的——小记录集几乎是瞬间完成的。

这是你正在经历的那种表演吗?如果是这样,它与我期望的从Excel到MDB后端的ADO连接是一致的。

如果您看到的性能比这慢得多,那么一定存在一些影响事物的本地环境条件。

onedaywhen 15 年前

我不认为你是在以貌取人。

在Access中,当您查看查询的数据视图时,会发生以下情况:

使用现有的开放连接 (并保持开放);
记录集已部分填充只有前几行(和保持开放;
部分结果集显示在专用于任务和针对本机数据访问进行了优化方法访问使用(直接使用访问数据库引擎DLL, 可能)。

在您的VBA代码中:

打开新连接(然后随后关闭并释放);
记录集已完全填充使用所有行(然后关闭和释放);
整个结果集被读取到使用非本机的Excel通用用户界面数据访问组件。

我认为最重要的一点是,Access中的数据视图在请求之前不会获取整个结果集,通常是通过导航到结果集中的最后一行。ADO将始终提取结果集中的所有行。

第二个最重要的是将提取的行(假设结果集是完整的)读取到ui元素中所花费的时间,而事实上Excel没有针对作业进行优化。

打开、关闭和释放连接和记录集不重要,但仍然是一个因素。

我认为您需要对流程的每个步骤进行一些计时,以找到瓶颈。与Access比较时,请确保获得完整的结果集,例如检查返回的行数。

Dan 15 年前

许多公式可能引用查询。尝试临时打开宏中的“手动计算”,并在完成所有查询更新后将其关闭。

这应该会加快一点速度,但仍然不能解决根本问题。

Thorsten 15 年前

如果你检索了大量的记录,它可以解释为什么 Range(CellPaste) 需要这么长时间。(如果在Access中执行查询,则不会检索所有记录,但如果执行copyFromRecordset,则需要所有记录。)

copyFromRecordset有一个maxRows参数:

Public Function CopyFromRecordset ( _
    Data As Object, _
    <OptionalAttribute> MaxRows As Object, _
    <OptionalAttribute> MaxColumns As Object _
) As Integer

如果将此设置为低值(如10或10左右)会更改性能,请尝试。

Philippe Grondier 15 年前

以下的转变或改进是什么?

打开后,将记录集保存为XML文件(rst.savetofile xxx),然后让Excel重新打开它。
打开后,将记录集数据放入数组(rst.getrows xxx),并将数组复制到活动工作表中。
并且,在任何时候,尽量减少所有的内存/访问要求:以只读、只读方式打开记录集,一旦数据在您身边,关闭连接,等等。

RIF 11 年前

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

它以闪电般的速度检索记录(<5秒),但突然间速度非常慢(检索一个记录需要15秒)。这就是我进入你岗位的原因。

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

一旦我关上它,所有的一切都重新变快了。

SwiftJr 9 年前

问题是10中有9次与您使用的光标类型/位置有关。

在网络连接上使用动态光标可以减慢数据检索速度,即使查询执行得非常快。

如果您希望快速获得大量数据,则需要在连接上使用cursorlocation=aduseclient。这意味着你只有一个静态的本地光标,所以你不会从其他用户那里得到实时更新。

但是-如果您只是在读取数据,那么您将保存ADO返回到每个单独记录的数据库以检查更改。

我最近改变了这个,因为我有一个简单的循环,填充了一个列表项,每个循环大约需要0.3秒。不是慢,但即使是1000条记录,也需要30秒!仅更改光标位置可使整个过程在1秒内完成。