performance - 尽管结果数量减少了 25%，但分组数据会减慢查询速度

Question

我有一个 400 万行的 Access 数据库，每行代表一个单独的客户订单。

我需要从 Excel 运行查询(我使用 VBA)，以便仅检索来自 REGION1 中客户的订单。 .

我尝试了以下(名称应该是不言自明的):

Sub Query()

Dim cn As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String

strFile = "C:\Users\MyName\Desktop\DataBase.accdb"
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon

strSQL = "SELECT [CUSTOMER], [DATE], [REVENUE]" _
& "FROM [SALES DB]" _
& "WHERE [REGION]='REGION1'"
rs.Open strSQL, cn, 0, 1

Worksheets(1).Cells(2, 1).CopyFromRecordset rs

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

这很好用，但它有点慢，因为它返回 ~600k 行。

所以我想:“谁关心所有客户订单的详细列表？我只需要每月汇总。这应该减少返回的行数，从而提高速度!”。

所以我将代码更改为:

strSQL = "SELECT [CUSTOMER], MONTH([DATE]), YEAR([DATE]), SUM([REVENUE])" _
& "FROM [SALES DB]" _
& "WHERE [REGION]='REGION1'"
& "GROUP BY [CUSTOMER], MONTH([DATE]), YEAR([DATE])"

正如我所料，现在只显示约 45 万个结果。问题是，查询实际上变慢了。

我实际上最好提取未分组的数据，然后用一个简单的数据透视表聚合它。

更少的数据如何提取更慢？我知道在两者之间需要执行一些计算，但仍然如此。

有没有人知道我如何克服这个问题？

Answer 1

您没有提到查询所花费的实际时间，但这里有一些想法:

确保您对要分组或过滤的数据库中的所有字段都有索引。

如果您是该数据库的唯一用户，请以独占模式打开它:
对于 ADO ，使用连接字符串:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Mode=12;"

使用 DAO而不是 ADO .DAO是 Access 原生的，通常速度更快。

首先是在 Excel 中添加对 Access Engine 的引用:

在 IDE 中，在 Tools > References 下，向下查看列表并检查:Microsoft Office 12.0 Access database engine Object Library .

如果您有 Access 2010，引用将是:Microsoft Office 14.0 Access database engine Object Library .

使用旧版本的 Access(2003 和更早版本)，改用 Jet 引擎(仅限 MDB 文件)，它将是:Microsoft DAO 3.6 Object Library .

然后使用下面的 VBA 代码将数据加载到您的工作表中:

    Public Sub LoadFromDb()
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        ' Open the database in Exclusive mode '
        Set db = DBEngine.OpenDatabase("C:\Users\MyName\Desktop\DataBase.accdb", True)
        ' Open the recordset as a snapshot, it's faster than the default dbDynaset '
        Set rs = db.OpenRecordset("SELECT CUSTOMER, DATE, REVENUE " & _
                "FROM [SALES DB] WHERE REGION='REGION1'", dbOpenSnapshot)
        ' Copy the recordset to the sheet '
        Worksheets(1).Cells(2, 1).CopyFromRecordset rs
        rs.Close
        db.Close
        Set rs = Nothing
        Set db = Nothing
    End Sub

如果您主要是从 Access 导入数据以在数据透视表中显示它们，则 Access 本身中的数据透视表可能会更好地为您服务。

关于这个主题，您是否知道可以拆分数据库以共享数据后端并使用免费的 Access Runtime允许您的所有用户查看您的报告并使用他们机器上的数据？

迁移到 SQL Server 或其他数据库可能/可能根本无法解决您的问题:

如果 SQL Server 在您的计算机上，则计算查询所需的资源或多或少与 MS Access 数据库在您的计算机上一样。

如果 SQL Server 在远程机器上，大部分时间将花在网络数据传输上。

您的瓶颈可能不是数据库，现在是将那么多数据导入电子表格本身的时候了。您可以尝试从 Access 本身执行查询，看看需要多长时间。

如果您有这么多数据要筛选，Excel 可能不是完成这项工作的最佳工具，而专门的报告或商业智能应用程序可能会为您提供更好的服务。
有很多开源和商业平台，例如:

http://www.pentaho.com/ (开源 + 商业)

http://www.jaspersoft.com/ (开源 + 商业)

http://www.gcpowertools.com/categories/Analysis (商业，开发者)