我在工作中继承了这个访问数据库,我正在寻找一种更快的方法来编写一些代码。目前运行该程序大约需要 1.5 分钟,我需要它更快。
目前我已将其全部设置为 DAvg 和 DCount,但问题是它基本上打开和关闭其使用的每一行的查询。就其本身而言很好,但我需要这样做 36 次。
Private Sub Form_Load()
On Error GoTo errhndlr
Me.Visible = True
Dim FY_EngMiss, Prev_EngMiss, LThree_EngMiss, LNine_EngMiss, FY_DraftMiss, Prev_DraftMiss, LThree_DraftMiss, LNine_DraftMiss As Integer
Dim FY_Eng, Prev_Eng, LThree_Eng, LNine_Eng, FY_Draft, Prev_Draft, LThree_Draft, LNine_Draft As Integer
Dim FY_Fups, Prev_Fups, LThree_Fups, LNine_Fups, FY_pjs, Prev_Pjs, LThree_Pjs, LNine_Pjs, EngMissed As Integer
Dim FY_Quote, Prev_Quote, LThree_Quote, LNine_Quote As Double
Dim FY_Avg, Prev_AVG, LThree_Avg, LNine_Avg As Double
Dim SummaryDate, FiscalDate, LThreeDate As Date
Dim rst As DAO.Recordset
Me.Printer.Orientation = acPRORLandscape
Me.SummaryDate = Forms!ReportForm.Form.SDate
Me.FiscalDate = Forms!ReportForm.Form.FiscalStartDate
SummaryDate = CDate(DateValue(Me.SummaryDate))
FiscalDate = CDate(DateValue(Me.FiscalDate))
LThreeDate = CDate(DateValue(DateAdd("m", -1, Me.SummaryDate)))
'Quality Metrics
Me.JW_DAYRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JW' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
Me.CD_DAYRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'CD' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
Me.FM_DAYRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'FM' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
Me.JP_DAYRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JP' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
Me.MB_DAYRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'MB' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
Me.BE_DAYRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'BE' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
Me.JW_DAYQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JW' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
Me.CD_DAYQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'CD' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
Me.FM_DAYQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'FM' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
Me.JP_DAYQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JP' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
Me.MB_DAYQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'MB' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
Me.BE_DAYQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'BE' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
Me.JW_MRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JW' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
Me.CD_MRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'CD' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
Me.FM_MRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'FM' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
Me.JP_MRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JP' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
Me.MB_MRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'MB' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
Me.BE_MRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'BE' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
Me.JW_MQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JW' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
Me.CD_MQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'CD' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
Me.FM_MQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'FM' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
Me.JP_MQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JP' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
Me.MB_MQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'MB' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
Me.BE_MQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'BE' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
Me.JW_YRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JW'")
Me.CD_YRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'CD'")
Me.FM_YRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'FM'")
Me.JP_YRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JP'")
Me.MB_YRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'MB'")
Me.BE_YRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'BE'")
Me.JW_YQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JW'")
Me.CD_YQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'CD'")
Me.FM_YQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'FM'")
Me.JP_YQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JP'")
Me.MB_YQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'MB'")
Me.BE_YQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'BE'")
Exit Sub
errhndlr:
Call Errhndler.ErrorHandler(Err.Number, Err.Description, Me.Name, "Form_Load")
End Sub
这也是 Email_Ranking 查询的 SQL 文本。
SELECT DISTINCTROW
archived_email.ProjectID,
archived_email.subject,
archived_email.approver,
archived_email.completeddate,
Avg(timestamp.projectrank) AS AvgOfProjectRank
FROM fy_current_external,
archived_email
INNER JOIN [timestamp]
ON archived_email.projectid = timestamp.projectid
GROUP BY archived_email.projectid,
archived_email.subject,
archived_email.approver,
archived_email.completeddate,
fy_current_external.date_value
HAVING ( ( ( archived_email.approver ) = "mb"
OR ( archived_email.approver ) = "fm"
OR ( archived_email.approver ) = "jw"
OR ( archived_email.approver ) = "be"
OR ( archived_email.approver ) = "cd"
OR ( archived_email.approver ) = "jp" )
AND ( ( archived_email.completeddate ) >= [date_value] )
AND ( ( LEFT([archived_email]![projectid], 2) ) = "e-"
OR ( LEFT([archived_email]![projectid], 2) ) = "pl" ) )
ORDER BY archived_email.completeddate DESC;
它正在搜索的查询包含 6,000 多个项目。
运行此代码的平均时间为 88 秒。此代码位于多个位置。我想将这个时间至少减少一半,最好减少到 10 秒或更短。