CODEWITHSUNDEEP
sqlsql-servert-sql
Daniel Martinez
Daniel Martinez

Reputation: 397

Speed up SQL query with nested query

I hope you guys can help me. I have the following SQL query, I think it's not very heavy but it takes about 5 minutes to complete. Please let me know if you have another way to complete this:

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))

Upvotes: 2

Views: 472

Answers (1)

sgeddes
sgeddes

Reputation: 62831

I do see some places your query can be rewritten, but I'm not sure how much it will help without looking at your execution plans and ensuring you have the appropriate indices in place.

For example, make sure you include actual join criteria in your first join to the compensationplan table. Do this by joining on compensationplan.Compensation_Plan_ID and rep.ID_Compensation_Plan.

Also, I don't see the need for the OUTER JOINs since you're using some of those tables in your correlated exist subquery.

Here is the updated query:

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)
    )

Upvotes: 2

Related Questions