Hmm,

There is a slight difference in the code , the 'fast' version includes the extra clause @pJobId > 0

The optimizer is probably promoting this to the JobWorkJob.JobOID which give a differenting plan.

FROM

JobStay

JOIN UDStay ON JobStay.JobOID = UDStay.StayOID

JOIN Job ON JobStay.JobOID = Job.JobOID

JOIN Item ON JobStay.ItemOID = Item.ItemOID

LEFT JOIN JobWorkJob ON JobWorkJob.ForJobOID = JobStay.JobOID

LEFT JOIN JOB WJ ON WJ.JobOID = JobWorkJob.JobOID

JOIN Commodity ON Commodity.CommodityOID = Item.CommodityOID

JOIN UDCommodity ON UDCommodity.CommodityOID = Commodity.CommodityOID

WHERE

@pJobOID > 0

AND

JobWorkJob.JobOID = @pJobOID

However.....

Is this really what you want to do ?

You are left joining to JobWorkJob and then filtering the result!

Should that be an inner join ?

Or an outer join ON ID and ID and JobOID = @pJobOID