Speed up SQL query with nested query

Question

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

Did you look at the execution plan?

juergen d
– juergen d

2013-07-13 16:39:04 +00:00
Commented Jul 13, 2013 at 16:39 — juergen d
– juergen d, Commented Jul 13, 2013 at 16:39
do you have proper indexes?

Darren Kopp
– Darren Kopp

2013-07-13 16:41:14 +00:00
Commented Jul 13, 2013 at 16:41 — Darren Kopp
– Darren Kopp, Commented Jul 13, 2013 at 16:41

sgeddes · Accepted Answer · 2013-07-13 16:49:13Z

2

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

answered Jul 13, 2013 at 16:49

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Martinez Over a year ago

I must say that with your query and re-checking my indexes (as your comment says), the query improved from 5minutes to less than 10secs.

Collectives™ on Stack Overflow

Speed up SQL query with nested query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related