Could anyone help me to reduce the query running time for the following query? If it involves VBA that's okay, I just need to get the correct result faster.
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] & "'");
One approach that might speed things up would be to create a saved query named [create_W_Data_summary] to perform the majority of the COUNTs and write them to a temporary table:
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]
resulting in a table named [W_Data_summary] containing rows like
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
Then your UPDATE query could total up the appropriate [row_count] values like so
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] & "'")
If you save that query as [update_A_Ticket] then your VBA code would be something like
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
Your UPDATE query would still be doing 24 domain aggregate operations for each row in [A_Ticket], but it would be doing them on the [W_Data_summary] table which would presumably have far fewer rows than the [W_Data] table.
The above assumed that [W_Data] was an Access table. If [W_Data] is an ODBC linked table to SQL Server then the process is slightly different:
Create a SQL Server stored procedure named [W_Data_rollup]
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
Create a saved pass-through query in Access named [get_W_Data_rollup]
EXEC dbo.W_Data_rollup '2013-11-11'
(Note that the initial date value is just a placeholder. It will be updated by the VBA code below.)
The make-table query [create_W_Data_summary] simply becomes
SELECT *
INTO W_Data_summary
FROM get_W_Data_rollup;
The update query [update_A_Ticket] remains as before
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] & "'");
and the VBA code to "make it go" is
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