Click here to Skip to main content
14,427,367 members
Rate this:
Please Sign up or sign in to vote.
See more:
how to write this query for good performance ?

I have query when i run it take may be 5 minutes to run

so i need to rewrite with good syntax for good performance

but i dont know how to enhance that query


What I have tried:

select p.partid,p.maskedid,m.chemicalid into #temp2 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  
update t1 set t1.status='conflict data' from #temp t1 inner join #temp2 t2 on  t1.maskid=t2.maskedid   
 so how to enhancement this query please ?
Posted
Updated 8-Jan-20 18:45pm
Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   
Comments
Maciej Los 9-Jan-20 1:55am
   
5ed!
Wendelius 10-Jan-20 14:15pm
   
Thank you!
Rate this:
Please Sign up or sign in to vote.

Solution 2

UPDATE t1 SET t1.status='conflict data'
FROM #temp t1 
LEFT OUTER join (select p.maskedid  
					FROM #temp t 
					LEFT OUTER JOIN parts p on  p.maskedid=t.maskId
					LEFT OUTER JOIN chemicalmaster m on m.partid=p.partid    
					WHERE p.maskedid IS NOT NULL AND m.partid IS NOT NULL 
					AND p.partid != t.partId
					AND m.chemicalid != t.chemicalId) t2 on  t1.maskid=t2.maskedid 
					
WHERE t2.maskedid IS NOT NULL
   
Comments
Maciej Los 9-Jan-20 1:55am
   
Off-topic!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100