使用 DCount 访问查询需要很长时间才能运行 [英] Access query using DCount takes a long time to run
本文介绍了使用 DCount 访问查询需要很长时间才能运行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
谁能帮我减少以下查询的查询运行时间?如果它涉及到 VBA 没关系，我只需要更快地得到正确的结果.
UPDATE A_Ticket SETI_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_Re = DCount("*","W_Data","[Priority] = 'S1' and [Type] <> 'R' and [R_Group] <> '1' and [R_Group] 不是 NULL 并且[产品]='" & [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_Re = DCount("*","W_Data","[Priority] = 'S2' and [Type] <> 'R' and [R_Group] <> '1' and [R_Group] 不是 NULL 并且[产品]='" & [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_Re = DCount("*","W_Data","[Priority] = 'S3' and [Type] <> 'R' and [R_Group] <> '1' and [R_Group] 不是 NULL 并且[产品]='" & [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_Re = DCount("*","W_Data","[Priority] = 'S4' and [Type] <> 'R' and [R_Group] <> '1' and [R_Group] 不是 NULL 并且[产品]='" & [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 和 [Product]='" & [Product_Name] & "'"),SR_I_S1_Resolved = DCount("*","W_Data","[Priority] = 'S1' and [Type] = 'R' and [R_Group] <> '1' and [R_Group] 不是 NULL 和 [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 和 [Product]='" & [Product_Name] & "'"),S_S2_Re = DCount("*","W_Data","[Priority] = 'S2' and [Type] = 'R' and [R_Group] <> '1' and [R_Group] 不为 NULL 且 [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 和 [Product]='" & [Product_Name] & "'"),S_S3_Re = DCount("*","W_Data","[Priority] = 'S3' and [Type] = 'R' and [R_Group] <> '1' and [R_Group] 不为 NULL 且 [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 和 [Product]='" & [Product_Name] & "'"),S_S4_Re = DCount("*","W_Data","[Priority] = 'S4' and [Type] = 'R' and [R_Group] <> '1' and [R_Group] 不为 NULL 且 [Product]='" & [Product_Name] & "'"); 解决方案
一种可能会加快速度的方法是创建一个名为 [create_W_Data_summary] 的已保存查询来执行大部分 COUNT 并将它们写入临时表:
PARAMETERS prm_StDate DateTime;选择[产品]，[优先事项]，[类型] = '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_countINTO [W_Data_summary]来自 [W_Data]通过...分组[产品]，[优先事项]，[类型] = 'R',[A_Group] <>'1',[R_Group] <>'1',[C_Date] <[prm_StDate]生成一个名为 [W_Data_summary] 的表，其中包含类似的行
产品优先级 is_Type_R A_Group_is_not_1 R_Group_is_not_1 early_than_StDate row_count——————————————————————————————------- -------------------- ---------产品1 S1 -1 -1 1产品1 S1 0 -1 -1 1产品1 S1 0 -1 -1 -1 2产品1 S1 0 -1 -1 0 1然后您的 UPDATE 查询可以像这样合计适当的 [row_count] 值
UPDATE A_Ticket SETI_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 early_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出错时继续下一步DoCmd.DeleteObject acTable, "W_Data_summary"出错时转到 0设置 cdb = CurrentDb设置 qdf = cdb.QueryDefs("create_W_Data_summary")qdf!prm_StDate = CDate(Forms!Home!Txt_StDate)qdf.执行设置 qdf = cdb.QueryDefs("update_A_Ticket")qdf.执行设置 qdf = 无设置 cdb = 无您的 UPDATE 查询仍会为 [A_Ticket] 中的每一行执行 24 次域聚合操作，但会在 [W_Data_summary] 表上执行这些操作，该表的行数可能远少于 [W_Data] 表.
SQL Server 链接表
以上假设 [W_Data] 是一个 Access 表.如果 [W_Data] 是到 SQL Server 的 ODBC 链接表，则过程略有不同:
创建一个名为 [W_Data_rollup] 的 SQL Server 存储过程
创建程序 [dbo].[W_Data_rollup]@StDate 日期作为开始设置无计数；选择[产品]，[优先事项]，is_Type_R,A_Group_is_not_1,R_Group_is_not_1，早于StDate，COUNT(*) AS row_count从(选择[产品]，[优先事项]，案件当 [Type] 为 NULL THEN NULL当 [类型] = 'R' THEN -1其他 0END AS is_Type_R,案件当 [A_Group] 为 NULL THEN NULLWHEN [A_Group] = '1' THEN 0其他 -1END AS A_Group_is_not_1，案件当 [R_Group] 为 NULL THEN NULLWHEN [R_Group] = '1' THEN 0其他 -1END AS R_Group_is_not_1，案件当 [C_Date] 为 NULL THEN NULL当 [C_Date] <@StDate 那么 -1其他 0END AS早于_StDate从 dbo.W_Data) 随便通过...分组[产品]，[优先事项]，is_Type_R,A_Group_is_not_1,R_Group_is_not_1，早于标准日期结尾去在 Access 中创建一个名为 [get_W_Data_rollup] 的已保存传递查询
EXEC dbo.W_Data_rollup '2013-11-11'(注意初始日期值只是一个占位符.它会被下面的 VBA 代码更新.)
生成表查询 [create_W_Data_summary] 简单地变成
SELECT *INTO W_Data_summary从 get_W_Data_rollup;更新查询[update_A_Ticket]保持不变
UPDATE A_Ticket SETI_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 early_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出错时继续下一步DoCmd.DeleteObject acTable, "W_Data_summary"出错时转到 0设置 cdb = CurrentDb设置 qdf = cdb.QueryDefs("get_W_Data_rollup")qdf.SQL = "EXEC dbo.W_Data_rollup '" &格式(CDate(Forms!Home!Txt_StDate), "yyyy-mm-dd") &'"设置 qdf = cdb.QueryDefs("create_W_Data_summary")qdf.执行设置 qdf = cdb.QueryDefs("update_A_Ticket")qdf.执行设置 qdf = 无设置 cdb = 无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.
edit re: SQL Server linked 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
这篇关于使用 DCount 访问查询需要很长时间才能运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
