MS访问功能需要很长时间才能完成存储过程

Question

create procedure _update_ticket
  @date datetime
as
begin
  update t set
    I_S1_O = (select count(*) from W_Data w where [Priority] = 'S1' and [Type] <> 'R' and [A_Group] <> '1' and [C_Date]< @date and w.[Product_Name] = t.[Product_Name]), 
    I_S1_R = (select count(*) from W_Data w where [Priority] = 'S1' and [Type] <> 'R' and [A_Group] <> '1' and [C_Date]>= @date and w.[Product_Name] = t.[Product_Name]), 
    I_S1_Re = (select count(*) from W_Data w where [Priority] = 'S1' and [Type] <> 'R' and [R_Group] <> '1' and [R_Group] is not NULL and w.[Product_Name] = t.[Product_Name]), 
    .....
  from ATicket t
end

当我使用访问表单中的以下函数执行上述存储过程时，需要很长时间...等了半个多小时。如果我打开直通查询，它会在几秒钟内更新。 请告知

Function AppCount()
Dim cdb As DAO.Database, qdf As DAO.QueryDef
On Error Resume Next
Set cdb = CurrentDb
Set qdf = cdb.QueryDefs("Get_A_Ticket")
qdf.SQL = "EXEC dbo.Update_A_Ticket_Count '" & CDate(Forms!Home!Txt_StDate) & "'"
qdf.Execute
Set qdf = Nothing
Set cdb = Nothing
End Function

Answer 1

问题很可能是更新语句中的所有子查询。 有时，只需创建正确的索引即可解决此问题。 但在这种情况下，这很难，因为条件上的所有不平等。

相反，您可以在W_Data中预先汇总数据，然后将这些数据重新联接以进行更新：

update t set
    I_S1_O = sumt.I_S1_O, 
    I_S1_R = sumt.I_S1_R,
    . . .
from ATicket t join
     (select Product_Name,
             sum(case when [Priority] = 'S1' and [Type] <> 'R' and [A_Group] <> '1' and [C_Date]< @date
                      then 1 else 0
                 end) I_S1_O,
             sum(case when [Priority] = 'S1' and [Type] <> 'R' and [A_Group] <> '1' and [C_Date]>= @date                          then 1 else 0
                 end) I_S1_R
      from W_Data w
      group by Product_Name
     ) sumt
     on t.Product_Name = sumt.Product_Name