How to replace (or Plid is null) with any thing more best practice?

Transact-SQL
,
1

I work on SQL server 2012 I face issue I need to remove statement ( plid is null or )
with any thing more good performance .

this is full script

I enter CodeType Mandatory but plid not mandatory meaning may be I entered or not
so main problem on my script using (or Plid is null)
I need to give any thing alternative to (or Plid is null)
because it make query more slow
so what I do please
this is dashboard display count for Parts based on PLID and CodeTypeId

CREATE TABLE #TempPlAndCodeType
(
CodeType NVARCHAR(100),
PlName NVARCHAR(100),
CodeTypeId int,
PLID   int,
[Status] Nvarchar(500)
)


create clustered index idx on #TempPlAndCodeType (CodeTypeId)
 
    insert into #TempPlAndCodeType(CodeType,PLID) values ('HTS-US',NULL)
   update c set c.CodeTypeId=a.AcceptedValuesOptionID from #TempPlAndCodeType c
   inner join Nop_AcceptedValuesOption a with (nolock) on
a.Name = c.CodeType and AcceptedValuesID=5652

  update c set c.PLID=a.AcceptedValuesOptionID from #TempPlAndCodeType c
   inner join Nop_AcceptedValuesOption a with (nolock) on
a.Name = c.PlName and AcceptedValuesID=110

create table #AllPlData
(
PLID  nvarchar(50),
PlIDName  nvarchar(500),    
PartFamilyId  int,
PartId  int,
CodeType nvarchar(50),
CodeTypeId int
)

insert into #AllPlData(PLID,PlIDName,PartFamilyId,PartId)
SELECT fmat.Value as PLID,acc.Name,fmat.PartFamilyID,pt.PartId

FROM
Parts.Nop_Part pt WITH(NOLOCK)  
INNER JOIN Parts.Nop_PartsFamilyAttribute fmat WITH(NOLOCK) ON  fmat.PartFamilyID=pt.PartsFamilyID AND fmat.[Key]=20281007
INNER JOIN dbo.Nop_AcceptedValuesOption acc WITH(NOLOCK) ON acc.AcceptedValuesOptionID=fmat.Value


create table #CountAllPartsPLs
(
 PLID nvarchar(50),
 CountAllPLParts  int,
 Pl  nvarchar(500),
 CodeType  nvarchar(50),
 CodeTypeId  int
)
insert into #CountAllPartsPLs (PLID,CountAllPLParts)
SELECT d.PLID,COUNT(d.PartID) AS CountAllPLParts
FROM
#AllPlData d
inner join #TempPlAndCodeType c on c.PLID is null OR d.PLID=c.PLID

GROUP BY d.PLID

update p set p.Pl=d.PlIDName from #CountAllPartsPLs p inner join #AllPlData d on d.PLID = P.PLID

select distinct t.PartId,c.CodeTypeId,c.CodeType,c.PlID,C.PlName,PartLevel into #tradecodes from parts.Tradecodes t
inner join #TempPlAndCodeType c on t.CodeTypeId=c.CodeTypeId


SELECT pt.PLID,tr.CodeTypeId,
COUNT(pt.PartID) [#partsHasCodes]
into #partsHasCodes
FROM  #tradecodes tr WITH(NOLOCK)
inner join #AllPlData pt  ON pt.PartID = tr.PartID
WHERE (tr.PLID is null OR pt.PLID=tr.PLID)
GROUP BY pt.PLID,tr.CodeTypeId


 
SELECT s.PLID,c.CodeTypeId,
sum(isnull(CountAllPLParts,0) - isnull([#partsHasCodes],0)) [#MissedpartsHasCodes] into #MissedpartsHasCodes
FROM #CountAllPartsPLs s left join  #partsHasCodes c on c.PLID=s.PLID
GROUP BY s.PLID,c.CodeTypeId


---------------------------
SELECT pt.PLID,pt.CodeTypeId,
COUNT(c.PartID ) AS [#partLevel]
into #TpartLevel
FROM #partsHasCodes pt
inner join parts.TradeCodes c on c.CodeTypeId=pt.CodeTypeid and
where c.PartLevel=1
GROUP BY pt.PLID,pt.CodeTypeId


------------------------------






SELECT s.PLID,c.CodeTypeId,
sum(isnull([#partsHasCodes],0) - isnull([#partLevel],0)) [#partGeneration] into #TpartGeneration
FROM #partsHasCodes s left join  #TpartLevel c on c.PLID=s.PLID --and c.CodeTypeid=s.CodeTypeid
GROUP BY s.PLID,c.CodeTypeId

 


SELECT  pt.PLID,t.CodeType
into #AllPLs
FROM
 #TempPlAndCodeType t
inner join #AllPlData pt  on (t.PLID is null OR pt.PLID=t.PLID)
left join Parts.TradeCodes tr WITH(NOLOCK) ON pt.PartID = tr.PartID
left join #TempPlAndCodeType c on (c.CodeTypeId=tr.CodeTypeID)
GROUP BY pt.PlIDName,t.CodeType,pt.PLID





select L.CodeType,ca.Pl,isnull(Ca.CountAllPLParts,0) as PlPartCount,isnull(c.[#partsHasCodes],0) as PartsHaveCodes,ISNULL(m.[#MissedpartsHasCodes],0) as MissedParts ,isnull(G.[#partGeneration],0) PartsHaveGeneration,isnull(P.[#partLevel],0)PartsOnPartLevel
from #AllPLs L
left join #CountAllPartsPLs Ca on L.PLID=Ca.PLID
left join #partsHasCodes C on L.PLID=c.PLID
left join #MissedpartsHasCodes m on L.PLID=m.PLID
left join #TpartGeneration G on G.PLID=C.PLID
left join #TpartLevel P on P.PLID=C.PLID  

drop table #partsHasCodes
drop table #TpartGeneration
drop table #TpartLevel
drop table #MissedpartsHasCodes
drop table #TempPlAndCodeType
drop table #AllPLs
drop table #CountAllPartsPLs
drop table #AllPlData
drop table #tradecodes
drop table #TempPlAndCodeType
2

hi ahmedbarbary

first you will have to find out whats going on at each step !!!
which steps are taking the most time and resources .. you will have to fix those !!

Don't know if you have experience in debugging !!

3

thanks for reply
I know debug
but what benefit here from using debugging

4

Plid is int one place varchar another place. That will cause some issues.

Which is is it?

When inserting into #tradecodes make it so that when null valuenof PLID is -9999

Then in where clause instead of is null you do plid = -9999

@JeffModen What say you, you jad commented on such an issue a while back

5

If I recall correctly, the previous issue I commented on a while back was for a "catch all" query where input parameters could be NULL. That's not what this post is about.

Another was about checking for things that are not blank or 0 and not NULL. Again, that's not what this post is about.

The only way to fix this particular problem for performance is to write each query that has an "OR" in the criteria twice, which is huge pain to write and maintain.

The best option, of course, is to not use NULLs to begin with.

6

How about coalesce()

7

That would be adding a function to a column used as criteria and that would make the query non-SARGable, which would be an even worse condition than it is now.

1 Like