使用嵌套查询加速SQL查询

Question

我希望你们能帮助我。我有以下SQL查询，我认为它不是很重，但它需要大约5分钟才能完成。如果您有其他方法完成此操作，请告诉我：

update rep
set rep.StatusID= 2,
rep.Available= 1,
rep.User= @user
from dbo.MCP_Rep_Compensation_Plan rep
left join dbo.MCP_Compensation_Plan compensationplan on compensationplan.Compensation_Plan_ID = @compplan_id and compensationplan.Active = 1
left join dbo.MRRS_Cycle actualcycle on actualcycle.CycleID = compensationplan.CycleID and actualcycle.Active = 1
left join dbo.MRRS_Cycle lastcycle on lastcycle.Consecutive = actualcycle.Consecutive -1 and lastcycle.Active = 1
where rep.Active = 1 and rep.ID_Compensation_Plan = @compplan_id and exists(
select OrderID
from dbo.MCP_Orders
where Active = 1 and Order_cycle = lastcycle.CycleID and OrderRepID = rep.RepID
and Order_Status in(28,30))

Answer 1

我确实看到您的查询可以重写的地方，但我不确定它将有多大帮助，如果不查看您的执行计划，并确保您有适当的索引到位。

例如，确保在第一次连接到薪酬计划表时包括实际连接条件。通过加入compensationplan.Compensation_Plan_ID和rep.ID_Compensation_Plan就可以做到这一点。

此外，我认为没有必要使用OUTER JOINs，因为您在相关的eXist子查询中使用了其中一些表。

以下是更新后的查询：

update rep
    set rep.StatusID= 2,
    rep.Available= 1,
    rep.User= @user
from dbo.MCP_Rep_Compensation_Plan rep
    join dbo.MCP_Compensation_Plan compensationplan on 
         compensationplan.Compensation_Plan_ID = rep.ID_Compensation_Plan and compensationplan.Active = 1
    join dbo.MRRS_Cycle actualcycle on 
          actualcycle.CycleID = compensationplan.CycleID and actualcycle.Active = 1
    join dbo.MRRS_Cycle lastcycle on 
          lastcycle.Consecutive = actualcycle.Consecutive -1 and lastcycle.Active = 1
where rep.Active = 1 
    and rep.ID_Compensation_Plan = @compplan_id 
    and exists(
        select OrderID
        from dbo.MCP_Orders
        where Active = 1 
            and Order_cycle = lastcycle.CycleID 
            and OrderRepID = rep.RepID
            and Order_Status in(28,30)
    )