How to optimize an Update Query using DCount and DSum

[kwvh]

I have a few queries that run against temporary tables that involve updating columns using DCount and DSum functions. These have always taken a long time to run, but now one of the temp tables being updated contains nearly 2000 records and it is taking over an hour to run. Because there were 11 columns being updated, I broke the query up into three separate queries, updating only 4 columns at a time. It is still taking half an hour to run just one of the three queries.

Is there a better method? Is there a way to optimize the queries?

FYI - Windows Vista Ultimate, Access 2003

Thanks in advance for your ideas.

Ken

[HansV]

2000 records is not a lot, so it surprises me that an update query should take so long. Can you give us an idea of the calculations involved, or post a stripped down, compacted and zipped copy of the database? (Remove sensitive data from the copy)

[kwvh]

Hans,
Thanks for getting back so promptly. Sorry for my delay, but I lost my internet connection. Below is one of the new SQL statements after breaking it up:

Code: Select all

UPDATE tblReportLagTimeDept SET tblReportLagTimeDept.LAG_numCurrMnth3 = IIf(DCount("pkey","7sqCY_WCReported","DeptCategory = " & Chr(34) & [Dept Category ] & Chr(34) & "and loc_lvl2_t=" & Chr(34) & [LAG_ProfitCenter] & Chr(34) & "AND [intDayToReport] Between 3 and 4 AND Month([occur_rdate])=Month([Forms]![frmReportSelection]![txtEndDate])")>=0,DCount("pkey","7sqCY_WCReported","DeptCategory = " & Chr(34) & [Dept Category ] & Chr(34) & "and loc_lvl2_t=" & Chr(34) & [LAG_ProfitCenter] & Chr(34) & "AND [intDayToReport] Between 3 and 4 AND Month([occur_rdate])=Month([Forms]![frmReportSelection]![txtEndDate])"),0),  tblReportLagTimeDept.LAG_numCurrMnth5 = IIf(DCount("pkey","7sqCY_WCReported","DeptCategory = " & Chr(34) & [Dept Category ] & Chr(34) & "and loc_lvl2_t=" & Chr(34) & [LAG_ProfitCenter] & Chr(34) & "AND [intDayToReport] > 4 AND Month([occur_rdate])=Month([Forms]![frmReportSelection]![txtEndDate])")>=0,DCount("pkey","7sqCY_WCReported","DeptCategory = " & Chr(34) & [Dept Category ] & Chr(34) & "and loc_lvl2_t=" & Chr(34) & [LAG_ProfitCenter] & Chr(34) & "AND [intDayToReport] > 4 AND Month([occur_rdate])=Month([Forms]![frmReportSelection]![txtEndDate])"),0),  tblReportLagTimeDept.LAG_numPYYTD1 = IIf(DCount("pkey","1sqClaimsReportedPY","DeptCategory = " & Chr(34) & [Dept Category ] & Chr(34) & "and loc_lvl2_t=" & Chr(34) & [LAG_ProfitCenter] & Chr(34) & "AND [intDayToReport] <3 AND [cov_major]='WC'")>=0,DCount("pkey","1sqClaimsReportedPY","DeptCategory = " & Chr(34) & [Dept Category ] & Chr(34) & "and loc_lvl2_t=" & Chr(34) & [LAG_ProfitCenter] & Chr(34) & "AND [intDayToReport] <3 AND [cov_major]='WC'"),0), tblReportLagTimeDept.LAG_numPYYTD3 = IIf(DCount("pkey","1sqClaimsReportedPY","DeptCategory = " & Chr(34) & [Dept Category ] & Chr(34) & "and loc_lvl2_t=" & Chr(34) & [LAG_ProfitCenter] & Chr(34) & "AND [intDayToReport] Between 3 AND 4  AND [cov_major]='WC'")>=0,DCount("pkey","1sqClaimsReportedPY","DeptCategory = " & Chr(34) & [Dept Category ] & Chr(34) & "and loc_lvl2_t=" & Chr(34) & [LAG_ProfitCenter] & Chr(34) & "AND [intDayToReport] Between 3 AND 4  AND [cov_major]='WC'"),0);

Is this sufficient, or should I upload the table/database?

Thanks for your consideration.

Ken

[HansV]

As far as I can tell, the IIf's aren't necessary. The result of DCount will always be a number >= 0, so the Else part of IIf will never be used. And In (3, 4) is probably a bit more efficient than Between 3 and 4. This means that you can reduce the SQL to

UPDATE tblReportLagTimeDept SET tblReportLagTimeDept.LAG_numCurrMnth3 = DCount("pkey","7sqCY_WCReported","DeptCategory = " & Chr(34) & [Dept Category ] & Chr(34) & "and loc_lvl2_t=" & Chr(34) & [LAG_ProfitCenter] & Chr(34) & "AND [intDayToReport] In (3, 4) AND Month([occur_rdate])=Month([Forms]![frmReportSelection]![txtEndDate])"), tblReportLagTimeDept.LAG_numCurrMnth5 = DCount("pkey","7sqCY_WCReported","DeptCategory = " & Chr(34) & [Dept Category ] & Chr(34) & "and loc_lvl2_t=" & Chr(34) & [LAG_ProfitCenter] & Chr(34) & "AND [intDayToReport] > 4 AND Month([occur_rdate])=Month([Forms]![frmReportSelection]![txtEndDate])"), tblReportLagTimeDept.LAG_numPYYTD1 = DCount("pkey","1sqClaimsReportedPY","DeptCategory = " & Chr(34) & [Dept Category ] & Chr(34) & "and loc_lvl2_t=" & Chr(34) & [LAG_ProfitCenter] & Chr(34) & "AND [intDayToReport] <3 AND [cov_major]='WC'"), tblReportLagTimeDept.LAG_numPYYTD3 = DCount("pkey","1sqClaimsReportedPY","DeptCategory = " & Chr(34) & [Dept Category ] & Chr(34) & "and loc_lvl2_t=" & Chr(34) & [LAG_ProfitCenter] & Chr(34) & "AND [intDayToReport] In (3, 4) AND [cov_major]='WC'");

It might be possible to simplify the query further, but I'd need to see the database to know for sure.