使用DCount进行访问查询需要很长时间

Question

有人能帮我减少以下查询的查询运行时间吗？如果涉及到VBA，没关系，我只需要更快地得到正确的结果。

UPDATE A_Ticket SET 
I_S1_O = DCount("*","W_Data","[Priority] = 'S1' and [Type] <> 'R' and [A_Group] <> '1' and [C_Date]< Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
I_S1_R = DCount("*","W_Data","[Priority] = 'S1' and [Type] <> 'R' and [A_Group] <> '1' and [C_Date]>= Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
I_S1_Re = DCount("*","W_Data","[Priority] = 'S1' and [Type] <> 'R' and [R_Group] <> '1' and [R_Group] is not NULL and [Product]='" & [Product_Name] & "'"), 
I_S2_O = DCount("*","W_Data","[Priority] = 'S2' and [Type] <> 'R' and [A_Group] <> '1' and [C_Date]< Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
I_S2_R = DCount("*","W_Data","[Priority] = 'S2' and [Type] <> 'R' and [A_Group] <> '1' and [C_Date]>= Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
I_S2_Re = DCount("*","W_Data","[Priority] = 'S2' and [Type] <> 'R' and [R_Group] <> '1' and [R_Group] is not NULL and [Product]='" & [Product_Name] & "'"), 
I_S3_O = DCount("*","W_Data","[Priority] = 'S3' and [Type] <> 'R' and [A_Group] <> '1' and [C_Date]< Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
I_S3_R = DCount("*","W_Data","[Priority] = 'S3' and [Type] <> 'R' and [A_Group] <> '1' and [C_Date]>= Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
I_S3_Re = DCount("*","W_Data","[Priority] = 'S3' and [Type] <> 'R' and [R_Group] <> '1' and [R_Group] is not NULL and [Product]='" & [Product_Name] & "'"), 
I_S4_O = DCount("*","W_Data","[Priority] = 'S4' and [Type] <> 'R' and [A_Group] <> '1' and [C_Date]< Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
I_S4_R = DCount("*","W_Data","[Priority] = 'S4' and [Type] <> 'R' and [A_Group] <> '1' and [C_Date]>= Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
I_S4_Re = DCount("*","W_Data","[Priority] = 'S4' and [Type] <> 'R' and [R_Group] <> '1' and [R_Group] is not NULL and [Product]='" & [Product_Name] & "'"), 
SR_I_S1_Open = DCount("*","W_Data","[Priority] = 'S1' and [Type] = 'R' and [A_Group] <> '1' and [C_Date]< Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
SR_I_S1_Received = DCount("*","W_Data","[Priority] = 'S1' and [Type] = 'R' and [A_Group] <> '1' and [C_Date]>= Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
SR_I_S1_Resolved = DCount("*","W_Data","[Priority] = 'S1' and [Type] = 'R' and [R_Group] <> '1' and [R_Group] is not NULL and [Product]='" & [Product_Name] & "'"), 
S_S2_O = DCount("*","W_Data","[Priority] = 'S2' and [Type] = 'R' and [A_Group] <> '1' and [C_Date]< Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
S_S2_R = DCount("*","W_Data","[Priority] = 'S2' and [Type] = 'R' and [A_Group] <> '1' and [C_Date]>= Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
S_S2_Re = DCount("*","W_Data","[Priority] = 'S2' and [Type] = 'R' and [R_Group] <> '1' and [R_Group] is not NULL and [Product]='" & [Product_Name] & "'"), 
S_S3_O = DCount("*","W_Data","[Priority] = 'S3' and [Type] = 'R' and [A_Group] <> '1' and [C_Date]< Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
S_S3_R = DCount("*","W_Data","[Priority] = 'S3' and [Type] = 'R' and [A_Group] <> '1' and [C_Date]>= Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
S_S3_Re = DCount("*","W_Data","[Priority] = 'S3' and [Type] = 'R' and [R_Group] <> '1' and [R_Group] is not NULL and [Product]='" & [Product_Name] & "'"), 
S_S4_O = DCount("*","W_Data","[Priority] = 'S4' and [Type] = 'R' and [A_Group] <> '1' and [C_Date]< Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
S_S4_R = DCount("*","W_Data","[Priority] = 'S4' and [Type] = 'R' and [A_Group] <> '1' and [C_Date]>= Forms!Home!Txt_StDate and [Product]='" & [Product_Name] & "'"), 
S_S4_Re = DCount("*","W_Data","[Priority] = 'S4' and [Type] = 'R' and [R_Group] <> '1' and [R_Group] is not NULL and [Product]='" & [Product_Name] & "'");

Answer 1

一种可能加快速度的方法是创建一个名为create_W_Data_summary的保存查询，以执行大部分计数，并将它们写入临时表：

PARAMETERS prm_StDate DateTime;
SELECT 
    [Product],
    [Priority],
    [Type] = 'R' AS [is_Type_R],
    [A_Group] <> '1' AS [A_Group_is_not_1],
    [R_Group] <> '1' AS [R_Group_is_not_1],
    [C_Date] < [prm_StDate] AS [earlier_than_StDate],
    COUNT(*) AS row_count
INTO [W_Data_summary]
FROM [W_Data]
GROUP BY
    [Product],
    [Priority],
    [Type] = 'R',
    [A_Group] <> '1',
    [R_Group] <> '1',
    [C_Date] < [prm_StDate]

生成一个名为W_Data_summary的表，该表包含以下行

Product   Priority  is_Type_R  A_Group_is_not_1  R_Group_is_not_1  earlier_than_StDate  row_count
--------  --------  ---------  ----------------  ----------------  -------------------  ---------
Product1  S1               -1                -1                                                 1
Product1  S1                0                -1                                     -1          1
Product1  S1                0                -1                -1                   -1          2
Product1  S1                0                -1                -1                    0          1

然后，您的更新查询可以汇总适当的row_count值，如

UPDATE A_Ticket SET 
I_S1_O = DSum("row_count","W_Data_summary","[Priority] = 'S1' AND (NOT is_Type_R) AND (A_Group_is_not_1) AND (earlier_than_StDate) AND [Product]='" & [Product_Name] & "'"), 
I_S1_R = DSum("row_count","W_Data_summary","[Priority] = 'S1' AND (NOT is_Type_R) AND (A_Group_is_not_1) AND (NOT earlier_than_StDate) AND [Product]='" & [Product_Name] & "'"), 
I_S1_Re = DSum("row_count","W_Data_summary","[Priority] = 'S1' AND (NOT is_Type_R) AND (R_Group_is_not_1) AND [Product]='" & [Product_Name] & "'")

如果将该查询保存为update_A_Ticket，则您的VBA代码将类似于

Dim cdb As DAO.Database, qdf As DAO.QueryDef
On Error Resume Next
DoCmd.DeleteObject acTable, "W_Data_summary"
On Error GoTo 0
Set cdb = CurrentDb
Set qdf = cdb.QueryDefs("create_W_Data_summary")
qdf!prm_StDate = CDate(Forms!Home!Txt_StDate)
qdf.Execute
Set qdf = cdb.QueryDefs("update_A_Ticket")
qdf.Execute
Set qdf = Nothing
Set cdb = Nothing

您的更新查询仍将对A_Ticket中的每一行执行24个域聚合操作，但它将在W_Data_summary表上执行这些操作，该表的行可能比W_Data表少得多。

编辑re: Server链接表

上面的假设是W_Data是一个访问表。如果W_Data是与Server相连的ODBC表，则进程略有不同：

创建名为W_Data_rollup的Server存储过程

CREATE PROCEDURE [dbo].[W_Data_rollup] 
    @StDate date
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
        [Product], 
        [Priority], 
        is_Type_R, 
        A_Group_is_not_1, 
        R_Group_is_not_1, 
        earlier_than_StDate,
        COUNT(*) AS row_count 
    FROM 
        (
            SELECT 
                [Product], 
                [Priority], 
                CASE 
                    WHEN [Type] IS NULL THEN NULL 
                    WHEN [Type] = 'R' THEN -1 
                    ELSE 0 
                END AS is_Type_R, 
                CASE 
                    WHEN [A_Group] IS NULL THEN NULL 
                    WHEN [A_Group] = '1' THEN 0 
                    ELSE -1 
                END AS A_Group_is_not_1, 
                CASE 
                    WHEN [R_Group] IS NULL THEN NULL 
                    WHEN [R_Group] = '1' THEN 0 
                    ELSE -1 
                END AS R_Group_is_not_1, 
                CASE 
                    WHEN [C_Date] IS NULL THEN NULL 
                    WHEN [C_Date] < @StDate THEN -1 
                    ELSE 0 
                END AS earlier_than_StDate
            FROM dbo.W_Data
        ) AS whatever
    GROUP BY
        [Product], 
        [Priority], 
        is_Type_R, 
        A_Group_is_not_1, 
        R_Group_is_not_1, 
        earlier_than_StDate
END

GO

在名为get_W_Data_rollup的Access中创建保存的传递查询。

EXEC dbo.W_Data_rollup '2013-11-11'

(请注意，初始日期值只是一个占位符。它将由下面的VBA代码更新。)

使表查询create_W_Data_summary变得简单。

SELECT * 
INTO W_Data_summary
FROM get_W_Data_rollup;

更新查询update_A_Ticket保持原样。

UPDATE A_Ticket SET 
    I_S1_O = DSum("row_count","W_Data_summary","[Priority] = 'S1' AND (NOT is_Type_R) AND (A_Group_is_not_1) AND (earlier_than_StDate) AND [Product]='" & [Product_Name] & "'"), 
    I_S1_R = DSum("row_count","W_Data_summary","[Priority] = 'S1' AND (NOT is_Type_R) AND (A_Group_is_not_1) AND (NOT earlier_than_StDate) AND [Product]='" & [Product_Name] & "'"), 
    I_S1_Re = DSum("row_count","W_Data_summary","[Priority] = 'S1' AND (NOT is_Type_R) AND (R_Group_is_not_1) AND [Product]='" & [Product_Name] & "'");

而VBA代码“让它前进”是

Dim cdb As DAO.Database, qdf As DAO.QueryDef
On Error Resume Next
DoCmd.DeleteObject acTable, "W_Data_summary"
On Error GoTo 0
Set cdb = CurrentDb
Set qdf = cdb.QueryDefs("get_W_Data_rollup")
qdf.SQL = "EXEC dbo.W_Data_rollup '" & Format(CDate(Forms!Home!Txt_StDate), "yyyy-mm-dd") & "'"
Set qdf = cdb.QueryDefs("create_W_Data_summary")
qdf.Execute
Set qdf = cdb.QueryDefs("update_A_Ticket")
qdf.Execute
Set qdf = Nothing
Set cdb = Nothing