How to make query get less time when run to take from 9 second to one second ?

Question

I work on sql server 2012
I have issue when run query it take 9 second to return only one row
so it is more because i will display may be 100000 rows
so for one row it will be huge time
so what I do

execution plan
https://www.brentozar.com/pastetheplan/?id=Sybl57QbO

                SELECT  
    m.partId,
    m.Revision_Id,
    m.ReflowTemperatureSource_Revision_Id,
    m.[MSLSource_Revision_id],
    m.[BaseMaterialRevisionID],
    m.[WaveTemperatureSource_Revision_ID],
    m.[ShelfLifeRevisionID]



FROM    #TempPC t


            inner JOIN Parts.ManufacturingData m WITH(NOLOCK) ON 

                 t.RevisionID in (Revision_Id,ReflowTemperatureSource_Revision_Id,[MSLSource_Revision_id],[BaseMaterialRevisionID],[WaveTemperatureSource_Revision_ID],[ShelfLifeRevisionID]) 

I use revisionid in (columns)
because i search on revision id on these columns
I use union all inplace of in
it take 24 second for return one row
so what i do to minimize time from 9 second to one second

Answer 1

See if it helps refactoring with UNION:

SELECT
    m.partId,
    m.Revision_Id,
    m.ReflowTemperatureSource_Revision_Id,
    m.[MSLSource_Revision_id],
    m.[BaseMaterialRevisionID],
    m.[WaveTemperatureSource_Revision_ID],
    m.[ShelfLifeRevisionID]
FROM ManufacturingData m
WHERE EXISTS (select 1 from #TempPC t where m.Revision_Id =t.RevisionID)
UNION
SELECT
    m.partId,
    m.Revision_Id,
    m.ReflowTemperatureSource_Revision_Id,
    m.[MSLSource_Revision_id],
    m.[BaseMaterialRevisionID],
    m.[WaveTemperatureSource_Revision_ID],
    m.[ShelfLifeRevisionID]
FROM ManufacturingData m
WHERE EXISTS (select 1 from #TempPC t where m.ReflowTemperatureSource_Revision_Id=t.RevisionID)
UNION
SELECT
    m.partId,
    m.Revision_Id,
    m.ReflowTemperatureSource_Revision_Id,
    m.[MSLSource_Revision_id],
    m.[BaseMaterialRevisionID],
    m.[WaveTemperatureSource_Revision_ID],
    m.[ShelfLifeRevisionID]
FROM ManufacturingData m
WHERE EXISTS (select 1 from #TempPC t where m.[MSLSource_Revision_id]=t.RevisionID)
UNION
SELECT
    m.partId,
    m.Revision_Id,
    m.ReflowTemperatureSource_Revision_Id,
    m.[MSLSource_Revision_id],
    m.[BaseMaterialRevisionID],
    m.[WaveTemperatureSource_Revision_ID],
    m.[ShelfLifeRevisionID]
FROM ManufacturingData m
WHERE EXISTS (select 1 from #TempPC t where m.[BaseMaterialRevisionID]=t.RevisionID)
UNION
SELECT
    m.partId,
    m.Revision_Id,
    m.ReflowTemperatureSource_Revision_Id,
    m.[MSLSource_Revision_id],
    m.[BaseMaterialRevisionID],
    m.[WaveTemperatureSource_Revision_ID],
    m.[ShelfLifeRevisionID]
FROM ManufacturingData m
WHERE EXISTS (select 1 from #TempPC t where m.[WaveTemperatureSource_Revision_ID]=t.RevisionID)
SELECT
    m.partId,
    m.Revision_Id,
    m.ReflowTemperatureSource_Revision_Id,
    m.[MSLSource_Revision_id],
    m.[BaseMaterialRevisionID],
    m.[WaveTemperatureSource_Revision_ID],
    m.[ShelfLifeRevisionID]
FROM ManufacturingData m
WHERE EXISTS (select 1 from #TempPC t where m.[ShelfLifeRevisionID]=t.RevisionID);

Answer 2

There needs to be indexes on all the columns in the IN list:

Revision_Id
ReflowTemperatureSource_Revision_Id
MSLSource_Revision_id
BaseMaterialRevisionID
WaveTemperatureSource_Revision_ID
ShelfLifeRevisionID

And that is a separate index per column - not a composite index.

Those indexes may not be enough - but it is a minimal requirement. If any of the columns are non-indexed, you will get a table-scan of the big table.

Answer 3

Hi @ahmed salah ,

Agreed with Erland, creating index on all columns from two tables listed in your query is an important and basic method to improve the performance.

Besides, you could consider to convert this query to another writing, for example using exists or in.

Please refer one example below and check whether it could be faster or not.

select m.partId,  
m.Revision_Id,  
m.ReflowTemperatureSource_Revision_Id,  
m.[MSLSource_Revision_id],  
m.[BaseMaterialRevisionID],  
m.[WaveTemperatureSource_Revision_ID],  
m.[ShelfLifeRevisionID]   
from ManufacturingData m WITH(NOLOCK)   
where exists  
(select 1 from #TempPC t where m.Revision_Id =t.RevisionID or m.ReflowTemperatureSource_Revision_Id=t.RevisionID or m.[MSLSource_Revision_id]=t.RevisionID  
or m.[BaseMaterialRevisionID]=t.RevisionID or m.[WaveTemperatureSource_Revision_ID]=t.RevisionID or m.[ShelfLifeRevisionID]=t.RevisionID)  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

i create index to all columns from both tables
and run this query
select m.partId,
m.Revision_Id,
m.ReflowTemperatureSource_Revision_Id,
m.[MSLSource_Revision_id],
m.[BaseMaterialRevisionID],
m.[WaveTemperatureSource_Revision_ID],
m.[ShelfLifeRevisionID]
from ManufacturingData m WITH(NOLOCK)
where exists
(select 1 from #TempPC t where m.Revision_Id =t.RevisionID or m.ReflowTemperatureSource_Revision_Id=t.RevisionID or m.[MSLSource_Revision_id]=t.RevisionID
or m.[BaseMaterialRevisionID]=t.RevisionID or m.[WaveTemperatureSource_Revision_ID]=t.RevisionID or m.[ShelfLifeRevisionID]=t.RevisionID)

it take 30 second for return one row
query i posted take 9 second and for one row
and i need to make it take less time for one row
so what i do