February 9, 2021 at 9:03 pm
How to replace OR statement on revision by another best practise and more performance ?
I work on SQL server 2012 I face issue I need to use any thing alternative or best from using OR statement multiple
time .
so How to use any solution without Using OR statement on Revision
with another meaning I need to replace that by any thing best practise and more performance
isnull(f.Revision_ID,'')=isnull(m.Revision_Id,'')
OR isnull(f.revision_id,'') = isnull(m.ReflowTemperatureSource_Revision_Id,'')
OR isnull(f.revision_id,'') = isnull(m.[MSLSource_Revision_id],'')
OR isnull(f.revision_id,'') = isnull(m.Revision_Id,'')
OR isnull(f.revision_id,'') = isnull(m.[ShelfLifeRevisionID],'')
OR isnull(f.revision_id,'') = isnull(m.[BaseMaterialRevisionID],'')
OR isnull(f.revision_id,'') = isnull(m.[WaveTemperatureSource_Revision_ID],'')
SELECT TOP 1000000
p.PartNumber PartNumber ,
c.CompanyName CompanyName ,
reflowv.online_url ReflowTemperatureSourceOnline ,
reflowv.local_url ReflowTemperatureSourceOffline ,
m.Comment
FROM #TempPC t
inner join #final f on t.RevisionID=f.Revision_Id
LEFT JOIN Parts.ManufacturingData m WITH(NOLOCK) ON isnull(f.Revision_ID,'')=isnull(m.Revision_Id,'')
OR isnull(f.revision_id,'') = isnull(m.ReflowTemperatureSource_Revision_Id,'')
OR isnull(f.revision_id,'') = isnull(m.[MSLSource_Revision_id],'')
OR isnull(f.revision_id,'') = isnull(m.Revision_Id,'')
OR isnull(f.revision_id,'') = isnull(m.[ShelfLifeRevisionID],'')
OR isnull(f.revision_id,'') = isnull(m.[BaseMaterialRevisionID],'')
OR isnull(f.revision_id,'') = isnull(m.[WaveTemperatureSource_Revision_ID],'')
LEFT JOIN [Z2URLSystem].[zsrc].[core_view] reflowv WITH(NOLOCK) ON isnull(reflowv.revision_id,'') = isnull(m.ReflowTemperatureSource_Revision_Id,'')
LEFT JOIN Parts.Nop_Part p WITH(NOLOCK) ON p.partid=m.partid
LEFT JOIN #Values va ON va.partid = p.PartID
February 10, 2021 at 12:31 pm
Wow.
So, the issue is not the OR clause. The issue is performing the ISNULL function on every single column as part of your JOIN. That optimizer has no choice but to scan the table. There's not an index in the world that's going to help. I would focus on getting rid of that. The only way to do that for sure is to clean the data and/or structure so you don't have to do ISNULL for all the data.
You're in a tough spot.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2021 at 5:34 pm
Why did you allow all those columns to be NULL-able? You can't default them to an empty string or blank or some other symbol instead in your DDL? And why did you failt to show any DDL? Why not use the M predicate after you've made your corrections to your DDL?
F.revision_id.
IN (M.revision_id, M.reflow_temperature_source_revision_id,
M. msl_source_revision_id, M.revision_id, M.shelflife_revision_id, M.base_material_revision_id, M.wave_temperature_source_r)
Please post DDL and follow ANSI/ISO standards when asking for help.
February 13, 2021 at 6:53 pm
as a important note get ride of those with(nolock). if your business is ok with possibility of having bad data supplied to them you can add a single statement at the top of your proc or on your connection string for the same effect.
regarding the OR's - can't be removed unless you rewrite the whole table Parts.ManufacturingData
but you can simplify the query as your inner join is by default excluding records with null values on revision_id due to the inner join to #tempPC which will automatically exclude any NULL
FROM #TempPC t
inner join #final f on t.RevisionID=f.Revision_Id
so
LEFT JOIN Parts.ManufacturingData m WITH(NOLOCK) ON isnull(f.Revision_ID,'')=isnull(m.Revision_Id,'')
OR isnull(f.revision_id,'') = isnull(m.ReflowTemperatureSource_Revision_Id,'')
can then be changed to
LEFT JOIN Parts.ManufacturingData m
ON f.Revision_ID =isnull(m.Revision_Id,'')
OR f.revision_id = isnull(m.ReflowTemperatureSource_Revision_Id,'')
do ensure that that temp table has a clustered index on revision_id and see if performance with it improves after the change above is done.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply