时间戳

首页 > 解决方案 > 改进 Excel VBA 中的 SQL 查询

sql - 改进 Excel VBA 中的 SQL 查询

问题描述

我在使用 SQL 访问的 Excel 工作簿中有 3 个表。
有一个 Inscriptions 表，其中包含AGENT_IDand表MLS_ID，PHOTOS其中包含最近提要中的所有照片，MLS_ID以及PHOTOS_CURRENT当前系统中的所有照片MLS_ID。
目标是查找新提要中是否有当前不在系统中的照片。

我试图查询使用NOT EXISTS和NOT IN方法。两者都需要很长时间才能运行（有时每个 2 分钟AGENT_ID）。

NOT EXISTS方法：

sqlQuery = "SELECT DISTINCT INSCR.MLS_ID FROM [INSCRIPTIONS_CURRENT$] INSCR, [PHOTOS$] P1 " & _
                "WHERE INSCR.AGENT_ID = " & inpAgentId & _
                " AND INSCR.MLS_ID = P1.MLS_ID AND NOT exists (select 1 from [PHOTOS_CURRENT$] PC1 where PC1.MLS_ID = P1.MLS_ID and PC1.PHOTO_ID = P1.PHOTO_ID)"

NOT IN方法：

sqlQuery = "SELECT DISTINCT INSCR.MLS_ID FROM [INSCRIPTIONS_CURRENT$] INSCR, [PHOTOS$] P1 " & _
                "WHERE INSCR.AGENT_ID = " & inpAgentId & _
                " AND INSCR.MLS_ID = P1.MLS_ID AND INSCR.MLS_ID NOT IN (select MLS_ID from [PHOTOS_CURRENT$] PC1 where PC1.MLS_ID = P1.MLS_ID and PC1.PHOTO_ID = P1.PHOTO_ID)"

DB连接如下：

Sub Connect()

    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.CommandTimeout = 120

End Sub

查询被发送到程序进行处理，如下所示：

Function select_query(sqlQuery As String) As ADODB.Recordset

    Dim objRecordset As ADODB.Recordset

    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1

    Set objRecordset = CreateObject("ADODB.Recordset")

    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ThisWorkbook.FullName & _
    ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

    objRecordset.Open sqlQuery, objConnection, adOpenStatic, adLockOptimistic, 
    adCmdText

    Set select_query = objRecordset

End Function

有什么建议可以提高性能吗？

标签： sqlexcelvba

解决方案

感谢@TimWilliams，您的评论对解决这个问题最有帮助。我最终做的是编写一个单独的例程，在提要加载期间，创建一个包含所有更改的照片的表格，如下所示：

sqlQuery = "INSERT INTO [PHOTO_UPDATES$] SELECT P1.* " & _
                "FROM [PHOTOS$] P1 LEFT JOIN [PHOTOS_CURRENT$] PC1 " & _
                "ON P1.MLS_ID = PC1.MLS_ID AND P1.PHOTO_ID = PC1.PHOTO_ID WHERE PC1.PHOTO_ID is NULL"

然后，在为每个代理创建工作列表时，会执行以下操作：

sqlQuery = "SELECT DISTINCT INSCR.MLS_ID " & _
                "FROM [PHOTO_UPDATES$] PU1 , [INSCRIPTIONS_CURRENT$] INSCR " & _
                "WHERE INSCR.AGENT_ID = " & inpAgentId & " " & _
                "AND PU1.MLS_ID = INSCR.MLS_ID "

这两个例程的运行时间都不到 1 秒。

推荐阅读