sql - 如何重写我的代码以更快地执行？

Question

我继承了一个我试图加快速度的数据库。解决方案是 SQL。问题....我不知道怎么写SQL...

目前它使用大量的 DCount 和 DAvg 来获得平均需要近 40 秒的结果。我尝试通过创建查询来减少代码中的条件数量，但仍然需要将近 40 秒才能运行。

Private Sub Form_Load()
On Error GoTo errhndlr
    Me.Visible = True
    Dim OTD, Total, FY_OTD, FY_Total As Integer
    Dim SummaryDate, FiscalDate As Date

    Me.SummaryDate = Forms!ReportForm.Form.SDate
    Me.FiscalDate = Forms!ReportForm.Form.FiscalStartDate
    SummaryDate = CDate(DateValue(Me.SummaryDate))
    FiscalDate = CDate(DateValue(Me.FiscalDate))
    Me.Printer.Orientation = acPRORLandscape

    ' Met OTD
    Me.OTD_Email = DCount("ProjectID", "Archived_Email", "Left([ProjectID],2) = 'E-' and [ReportDate] like '" & SummaryDate & "' and DateDiff('s',[ETA],[CompletedDate]) < 0 and [Flo_Thru_Email] = True")
    Me.OTD_PLEmail = DCount("ProjectID", "Archived_Email", "Left([ProjectID],2) = 'PL' and [ReportDate] like '" & SummaryDate & "' and DateDiff('s',[ETA],[CompletedDate]) < 0 and [Flo_Thru_Email] = True")
    Me.OTD_Submission = DCount("ProjectID", "Archived_Email", "Left([ProjectID],2) = 'DE' and [ReportDate] like '" & SummaryDate & "' and DateDiff('s',[ETA],[CompletedDate]) < 0")
    Me.OTD_LT = DCount("ProjectID", "Archived_Projects", "Left([ProjectID],3) = 'LT-' and Format([Sent_to_Rep], 'm/d/yyyy') = '" & SummaryDate & "' and DateDiff('s',[ETA],[Sent_to_Rep]) < 0 and [Approver] = 'CD' or Left([ProjectID],3) = 'LT-' and Format([Sent_to_Rep], 'm/d/yyyy') = '" & SummaryDate & "' and DateDiff('s',[ETA],[Sent_to_Rep]) < 0 and [Approver] = 'MB' or Left([ProjectID],3) = 'LT-' and Format([Sent_to_Rep], 'm/d/yyyy') = '" & SummaryDate & "' and DateDiff('s',[ETA],[Sent_to_Rep]) < 0 and [Approver] = 'JP'")
    Me.OTD_Specifier = DCount("ProjectID", "Archived_Email", "Left([ProjectID],3) = 'SSE' and [ReportDate] like '" & SummaryDate & "' and DateDiff('s',[ETA],[CompletedDate]) < 0")

    ' Total
    Me.Total_Phone = DCount("ProjectID", "Phone_Info", "Format([EndDate], 'm/d/yyyy') = '" & SummaryDate & "' and left([ProjectID], 2) = 'WI' and [EmployeeID] = 'CD' or Format([EndDate], 'm/d/yyyy') = '" & SummaryDate & "' and left([ProjectID], 2) = 'WI' and [EmployeeID] = 'JP' or Format([EndDate], 'm/d/yyyy') = '" & SummaryDate & "' and left([ProjectID], 2) = 'WI' and [EmployeeID] = 'MB' ")
    Me.Total_Email = DCount("ProjectID", "Archived_Email", "Left([ProjectID],2) = 'E-' and [ReportDate] like '" & SummaryDate & "' and [Flo_Thru_Email] = True")
    Me.Total_PLEmail = DCount("ProjectID", "Archived_Email", "Left([ProjectID],2) = 'PL' and [ReportDate] like '" & SummaryDate & "' and [Flo_Thru_Email] = True")
    Me.Total_Submission = DCount("ProjectID", "Archived_Email", "Left([ProjectID],2) = 'DE' and [ReportDate] like '" & SummaryDate & "'")
    Me.Total_LT = DCount("ProjectID", "Archived_Projects", "Left([ProjectID],3) = 'LT-' and Format([Sent_to_Rep], 'm/d/yyyy') = '" & SummaryDate & "' and [Approver] = 'CD' or Left([ProjectID],3) = 'LT-' and Format([Sent_to_Rep], 'm/d/yyyy') = '" & SummaryDate & "' and [Approver] = 'MB' or Left([ProjectID],3) = 'LT-' and Format([Sent_to_Rep], 'm/d/yyyy') = '" & SummaryDate & "' and [Approver] = 'JP'")
    Me.Total_Specifier = DCount("ProjectID", "Archived_Email", "Left([ProjectID],3) = 'SSE' and [ReportDate] like '" & SummaryDate & "'")

    '% OTD
    If Me.Total_Email > 0 Then
    Me.PercentOTD_Email = Me.OTD_Email / Me.Total_Email
    Else: Me.PercentOTD_Email = "0"
    End If

    If Me.Total_PLEmail > 0 Then
    Me.PercentOTD_PLEmail = Me.OTD_PLEmail / Me.Total_PLEmail
    Else: Me.PercentOTD_PLEmail = "0"
    End If

    If Me.Total_Submission > 0 Then
    Me.PercentOTD_Submission = Me.OTD_Submission / Me.Total_Submission
    Else: Me.PercentOTD_Submission = "0"
    End If

    If Me.Total_LT > 0 Then
    Me.PercentOTD_LT = Me.OTD_LT / Me.Total_LT
    Else: Me.PercentOTD_LT = "0"
    End If

    If Me.Total_Specifier > 0 Then
    Me.PercentOTD_Specifier = Me.OTD_Specifier / Me.Total_Specifier
    Else: Me.PercentOTD_Specifier = "0"
    End If

    'FY % OTD
    Dim FY_EmailMet, FY_EmailTotal, FY_PLEmailMet, FY_PLEmailTotal, FY_DrEmailMet, FY_DrEmailTotal, FY_LTMet, FY_LTTotal, FY_SSMet, FY_SSTotal As Integer

        ' FYEmail
        FY_EmailMet = DCount("ProjectID", "Archived_Email", "Left([ProjectID],2) = 'E-' and [ReportDate] Between #" & SummaryDate & "# And #" & FiscalDate & "# and DateDiff('s',[ETA],[CompletedDate]) < 0 and [Flo_Thru_Email] = True")
        FY_PLEmailMet = DCount("ProjectID", "Archived_Email", "Left([ProjectID],2) = 'PL' and [ReportDate] Between #" & SummaryDate & "# And #" & FiscalDate & "# and DateDiff('s',[ETA],[CompletedDate]) < 0 and [Flo_Thru_Email] = True")
        FY_DrEmailMet = DCount("ProjectID", "Archived_Email", "Left([ProjectID],2) = 'DE' and [ReportDate] Between #" & SummaryDate & "# And #" & FiscalDate & "# and DateDiff('s',[ETA],[CompletedDate]) < 0")
        FY_LTMet = DCount("ProjectID", "Archived_Projects", "Left([ProjectID],3) = 'LT-' and [Sent_to_Rep] Between #" & SummaryDate + 1 & "# And #" & FiscalDate & "# and DateDiff('s',[ETA],[Sent_to_Rep]) < 0 and [Approver] = 'CD' or Left([ProjectID],3) = 'LT-' and [Sent_to_Rep] Between #" & SummaryDate + 1 & "# And #" & FiscalDate & "# and DateDiff('s',[ETA],[Sent_to_Rep]) < 0 and [Approver] = 'MB' or Left([ProjectID],3) = 'LT-' and [Sent_to_Rep] Between #" & SummaryDate + 1 & "# And #" & FiscalDate & "# and DateDiff('s',[ETA],[Sent_to_Rep]) < 0 and [Approver] = 'JP'")
        FY_SSMet = DCount("ProjectID", "Archived_Email", "Left([ProjectID],3) = 'SSE' and [ReportDate] Between #" & SummaryDate & "# And #" & FiscalDate & "# and DateDiff('s',[ETA],[CompletedDate]) < 0")

        FY_EmailTotal = DCount("ProjectID", "Archived_Email", "Left([ProjectID],2) = 'E-' and [ReportDate] Between #" & SummaryDate & "# And #" & FiscalDate & "# and [Flo_Thru_Email] = True")
        FY_PLEmailTotal = DCount("ProjectID", "Archived_Email", "Left([ProjectID],2) = 'PL' and [ReportDate] Between #" & SummaryDate & "# And #" & FiscalDate & "# and [Flo_Thru_Email] = True")
        FY_DrEmailTotal = DCount("ProjectID", "Archived_Email", "Left([ProjectID],2) = 'DE' and [ReportDate] Between #" & SummaryDate & "# And #" & FiscalDate & "#")
        FY_LTTotal = DCount("ProjectID", "Archived_Projects", "Left([ProjectID],3) = 'LT-' and [Sent_to_Rep] Between #" & SummaryDate + 1 & "# And #" & FiscalDate & "# and [Approver] = 'CD' or Left([ProjectID],3) = 'LT-' and [Sent_to_Rep] Between #" & SummaryDate + 1 & "# And #" & FiscalDate & "# and [Approver] = 'MB' or Left([ProjectID],3) = 'LT-' and [Sent_to_Rep] Between #" & SummaryDate + 1 & "# And #" & FiscalDate & "# and [Approver] = 'JP'")
        FY_SSTotal = DCount("ProjectID", "Archived_Email", "Left([ProjectID],3) = 'SSE' and [ReportDate] Between #" & SummaryDate & "# And #" & FiscalDate & "#")

    If FY_EmailTotal > 0 Then
    Me.FYPercentOTD_Email = FY_EmailMet / FY_EmailTotal
    Else: Me.FYPercentOTD_Email = "0"
    End If

    If FY_PLEmailTotal > 0 Then
    Me.FYPercentOTD_PLEmail = FY_PLEmailMet / FY_PLEmailTotal
    Else: Me.FYPercentOTD_PLEmail = "0"
    End If

    If FY_DrEmailTotal > 0 Then
    Me.FYPercentOTD_Submission = FY_DrEmailMet / FY_DrEmailTotal
    Else: Me.FYPercentOTD_Submission = "0"
    End If

    If FY_LTTotal > 0 Then
    Me.FYPercentOTD_LT = FY_LTMet / FY_LTTotal
    Else: Me.FYPercentOTD_LT = "0"
    End If

    If FY_SSTotal > 0 Then
    Me.FYPercentOTD_Specifier = FY_SSMet / FY_SSTotal
    Else: Me.FYPercentOTD_Specifier = "0"
    End If


    ' Avg Request Age

        Me.ReqAge_Email = DAvg("ReqAge", "Archived_Email", "Left([ProjectID],2) = 'E-' and [ReportDate] Between #" & SummaryDate & "# And #" & FiscalDate & "# and [Flo_Thru_Email] = True")
        Me.ReqAge_PLEmail = DAvg("ReqAge", "Archived_Email", "Left([ProjectID],2) = 'PL' and [ReportDate] Between #" & SummaryDate & "# And #" & FiscalDate & "# and [Flo_Thru_Email] = True")
        Me.Req_Age_DraftEmail = DAvg("ReqAge", "Archived_Email", "Left([ProjectID],2) = 'DE' and [ReportDate] Between #" & SummaryDate & "# And #" & FiscalDate & "#")
        Me.ReqAge_LT = DAvg("ReqAge", "Archived_Projects", "Left([ProjectID],3) = 'LT-' and [Sent_to_Rep] Between #" & SummaryDate + 1 & "# And #" & FiscalDate & "# and [Approver] = 'CD' or Left([ProjectID],3) = 'LT-' and [Sent_to_Rep] Between #" & SummaryDate + 1 & "# And #" & FiscalDate & "# and [Approver] = 'MB' or Left([ProjectID],3) = 'LT-' and [Sent_to_Rep] Between #" & SummaryDate + 1 & "# And #" & FiscalDate & "# and [Approver] = 'JP'")
        Me.ReqAge_SSEmail = DAvg("ReqAge", "Archived_Email", "Left([ProjectID],3) = 'SSE' and [ReportDate] Between #" & SummaryDate & "# And #" & FiscalDate & "#")


    ' Summary
    Me.TotalProjects = Me.Total_Email + Me.Total_PLEmail + Me.Total_Submission + Me.Total_LT + Me.Total_Specifier
    Me.PercentOTD_Engineering = (Me.OTD_Email + Me.OTD_PLEmail + Me.OTD_Submission + Me.OTD_LT + Me.OTD_Specifier) / Me.TotalProjects
    Me.FYPercentOTD_Engineering = (FY_EmailMet + FY_PLEmailMet + FY_DrEmailMet + FY_LTMet + FY_SSMet) / (FY_EmailTotal + FY_PLEmailTotal + FY_DrEmailTotal + FY_LTTotal + FY_SSTotal)

    Set rst = CurrentDb.OpenRecordset("Gemba_OTDs", dbOpenDynaset)
    With rst
        .FindFirst "[Report_Date] = '" & DateValue(Now) & "'"
        If Not .NoMatch Then
            .Edit
            .Fields("FT_EngineeringOTD") = Me.FYPercentOTD_Engineering
            .Fields("FT_PlatinumOTD") = Me.FYPercentOTD_PLEmail
            .Fields("FT_PlatinumAge") = Me.ReqAge_PLEmail
            .Fields("FT_EmailOTD") = Me.FYPercentOTD_Email
            .Fields("FT_EmailAge") = Me.ReqAge_Email
            Else
            .AddNew
            .Fields("Report_Date") = DateValue(Now)
            .Fields("FT_EngineeringOTD") = Me.FYPercentOTD_Engineering
            .Fields("FT_PlatinumOTD") = Me.FYPercentOTD_PLEmail
            .Fields("FT_PlatinumAge") = Me.ReqAge_PLEmail
            .Fields("FT_EmailOTD") = Me.FYPercentOTD_Email
            .Fields("FT_EmailAge") = Me.ReqAge_Email

        End If
        .Update
    End With
    Set rst = Nothing
Exit Sub
errhndlr:
    Call Errhndler.ErrorHandler(Err.Number, Err.Description, Me.Name, "Form_Load")
End Sub

我希望能够将时间缩短到 20 秒或更短，并学习一点关于用 SQL 编写的知识。大约还有 7 个报告使用这种类型的代码，都需要重写。

标签： sqlvbams-access

Answer 1

由于您不了解 SQL 并且您的 Access 知识处于中等水平，因此暂时忘记 SQL，并且肯定会忘记它作为您问题的“解决方案”。
因为人们的知识有限，他们听说所有大公司都在使用 MsSQL、Oracle、MySQL、PostGre ......他们认为它们是魔杖......它将“触摸”丑小鸭并将其变成一只美丽的天鹅...... .
好吧，我不想告诉你，情况并非如此......SQL很棒......但除非你把它当作数据库服务器，否则你会发现它会慢得多被低估的Ms Access...... . 不相信我...只需将一个大表（有几百万条记录）上传到 SQL 并尝试开始打开它并将其与您的 Access 进行比较....（如果它打开那将是一个小奇迹） ...至于 CRUD ... 算了吧...
现在每个人都会大喊……等等……但我们正在谈论 SQL Server……是的，我知道……但正如我上面所说，除非你把它当作数据库服务器，否则它不会产生你所期望的奇迹.
至于你的情况......我看到很多域聚合（DAvg，Dcount......）这些是最糟糕的......它们是绝对的性能杀手......开始组织你的数据，这样你就可以获得计数/平均值/通过标准 Access SQL 的 Max/Min，您将看到性能会飞...