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