The performance of a SQL statement is a combination of several factors. The key things include:
- Logic of the statement
- Amount of data in each table
- Selectivity of the operations
- Indexing
The way a single statement is written often plays a minor role because the job of the optimizer id to analyze variations of different executions plans based on different syntactical changes.
For the query to performance properly, the easiest thing is to concentrate on indexing. Good indexes ensure proper performance. However, creating and maintaining them causes overhead.
You seem to use a lot of temporary tables. If these tables are used only in this scenario, indexing them is not necessarily helping because creating the index might be slow. On the other hand if these tables are used constantly without recreating them, then indexing could help.
If you decide to create indexes, first candidates could be
- parts.maskid
- #temp.maskid
- chemicalmaster.partid
- chemicalmaster.chemicalid
However, I have doubts about these since you use a lot of inequality operators...
Another thing could be to combine the select and the update. Something like
update t1 set t1.status='conflict data'
from #temp t1
inner join (select p.partid,p.maskedid,m.chemicalid
from #temp t
inner join parts p on p.maskedid=t.maskId
and p.partid<>t.partId
inner join chemicalmaster m on m.partid=p.partid
and m.chemicalid<>t.chemicalId) t2 on t1.maskid = t2.maskedid
This could help the engine to do the work in a single run
But I believe, the biggest benefit would be gained if you can get completely rid of the temporary tables. However, in order to do this the whole logic of the batch should be analyzed.