You don't change the join just to shorten the execution time of the query!
Using an inner join in this case will mean that cm.partid will never
be null and changing the query in that way completely changes its behaviour.
You should instead look to other ways of achieving the goal
- Ensure that tables ExtractReports.dbo.FinalComplanceDataDelivery and Parts.ChemicalMaster both have indexes on their respective partid columns
- Instead of using
set r.HasChemical=case when cm.partid is not null then 'HasChemical' else 'No' end
try the slightly more efficient (and more readable)
set r.HasChemical=IsNull(cm.partid, 'No')
- Instead of using strings 'HasChemical' (shouldn't that be 'Yes' anyway??) and 'No' consider using a bit column type with 0 = False/No and 1 = True/Yes
- If column r.HasChemical is set up with a default (really easy if you use a bit column type) implying 'No'/False/0 then you can change the join to an Inner join and only update the column where HasChemical = 'HasChemical'/True/1 e.g.
update r set r.HasChemical='HasChemical'
from ExtractReports.dbo.FinalComplanceDataDelivery r with(nolock)
inner join Parts.ChemicalMaster cm with(nolock) on cm.partid=r.partid