How to write this dashboard with another way to avoid slow running ?

Question

I work on SQL server 2012 I face issue dashboard is very slow running
my input code type and pl
some times i filter y code type only as my sample below
but problem on statement below is very slow
so How to solve issue please

this statement is work very slow :

SELECT pt.PLID,tr.CodeTypeId,
COUNT(pt.PartID) [#partsHasCodes]
into #partsHasCodes
FROM  parts.tradecodes tr WITH(NOLOCK)
inner join #AllPlData pt  ON pt.PartID = tr.PartID
inner join #TempPlAndCodeType c on c.CodeTypeId=tr.CodeTypeId and (ISNULL(c.PLID,0)=0 OR pt.PLID=c.PLID)
GROUP BY pt.PLID,tr.CodeTypeId

Execution Plan

https://www.brentozar.com/pastetheplan/?id=rkNZAc8LP

Data for test as below :

CREATE TABLE [Parts].[TradeCodes](
    [TradeCodesID] [int] IDENTITY(1,1) NOT NULL,
    [PartID] [int] NOT NULL,
    [CodeTypeID] [int] NOT NULL,
    [PartLevel] [tinyint] NULL,

 CONSTRAINT [PK__TradeCod__FEFAF27527F7A1C3] PRIMARY KEY CLUSTERED 
(
    [TradeCodesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UC_PartCode] UNIQUE NONCLUSTERED 
(
    [PartID] ASC,
    [CodeTypeID] ASC,
    [PartLevel] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [Parts].[TradeCodes](PartID,CodeTypeID,PartLevel)
VALUES
(17309008,854838,1),
(17307734,854838,1),
(17307636,854838,1),
(17308140,854838,1),
(18827850,849774,1),
(1161994,849774,1),
(10548030,849774,1),
(789101,849774,1)
SELECT TOP 10000 * FROM PARTS.Nop_Part
WHERE PARTID IN (17309008,17307734,17307636,17308140,18827850,1161994,10548030,789101)

CREATE TABLE [Parts].[Nop_Part](
    [PartID] [int] NOT NULL PRIMARY KEY,
    [PartNumber] [nvarchar](70) NOT NULL,
    [PartsFamilyID] [int] NOT NULL,


) 
INSERT INTO [Parts].[Nop_Part](PartID,PartNumber,PartsFamilyID)
VALUES
(17309008,'101673-HMC252QS24',6453885),
(17307734,'101675-HMC544',6453888),
(17307636,'101785-HMC207AS8',6453890),
(17308140,'101830-HMC208AMS8',6453891),
(18827850,'24AA00/S',516718),
(1161994,'24AA00/SN',516718),
(10548030,'AD6643-250EBZ',6697956),
(789101,'AD6649EBZ',6697957),
(52135,'106TMA063M',3585),
(52138,'106TMA200M',3585),
(9274,'LT1818CS5#TRMPBF',2483)


CREATE TABLE [dbo].[Nop_AcceptedValuesOption](
    [AcceptedValuesOptionID] [int] NOT NULL PRIMARY KEY,
    [AcceptedValuesID] [int] NOT NULL,
    [Name] [nvarchar](500) NOT NULL,

 CONSTRAINT [PK_Nop_AcceptedValuesOption] PRIMARY KEY CLUSTERED 
(
    [AcceptedValuesOptionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [dbo].[Nop_AcceptedValuesOption] (AcceptedValuesOptionID,AcceptedValuesID,Name) VALUES
(854838,5652,'ECCN-US'),
(849774,5652,'HTS-US'),
(4044,110,'Op Amps'),
(4070,110,'EEPROM'),
(4126,110,'Aluminum Electrolytic Capacitors'),
(29085,110,'RF Evaluation/Development Kits and Boards')

CREATE TABLE [Parts].[Nop_PartsFamilyAttribute](
    [PartFamilyAttributeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [PartFamilyID] [int] NOT NULL,
    [Key] [int] NOT NULL,
    [Value] [nvarchar](max) NOT NULL,

 CONSTRAINT [PK_Nop_PartsFamilyAttribute30] PRIMARY KEY CLUSTERED 
(
    [PartFamilyAttributeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UK_PartFamilyID_Key30] UNIQUE NONCLUSTERED 
(
    [PartFamilyID] ASC,
    [Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

INSERT INTO [Parts].[Nop_PartsFamilyAttribute](PartFamilyID,[Key],Value)
VALUES
(6453885,20281007,29085),
(6453888,20281007,29085),
(6453890,20281007,29085),
(6453891,20281007,29085),
(516718,20281007,4070),
(6697956,20281007,29085),
(6697957,20281007,29085),
(2483,20281007,4044),
(3585,20281007,4126)

all dashboard data :

 CREATE TABLE #TempPlAndCodeType
(
CodeType NVARCHAR(100),
PlName NVARCHAR(100),
CodeTypeId int,
PLID   int,
[Status] Nvarchar(500)
)
 insert into    #TempPlAndCodeType(CodeType,PlName)
    values 
    ('ECCN-US',NULL),
    ('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  int,
    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
where acc.AcceptedValuesID=110


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

GROUP BY d.PLID,c.CodeTypeId,c.CodeType



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


SELECT pt.PLID,tr.CodeTypeId,
COUNT(pt.PartID) [#partsHasCodes]
into #partsHasCodes
FROM  parts.tradecodes tr WITH(NOLOCK)
inner join #AllPlData pt  ON pt.PartID = tr.PartID
inner join #TempPlAndCodeType c on c.CodeTypeId=tr.CodeTypeId and (ISNULL(c.PLID,0)=0 OR pt.PLID=c.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,tr.CodeTypeId,
COUNT(pt.PartID ) AS [#partLevel]
into #TpartLevel
FROM Parts.#tradecodes tr 
INNER JOIN #AllPlData pt  ON pt.PartId = tr.PartId
INNER JOIN #partsHasCodes hc  ON pt.PLID = hc.PLID
where tr.PartLevel=1 
GROUP BY pt.PLID,tr.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 
GROUP BY s.PLID,c.CodeTypeId















select L.CodeType,L.Pl,isnull(L.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 #CountAllPartsPLs L
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

Accepted Answer

Hi @ahmed salah ,

Only according all information you provided, you could have a try with below methods to improve the performance of query.

Remove WITH(NOLOCK).
Change the table scan to table seek.
For example: create nonclustered index on table [#AllPlData] with two columns PLID and partid.
Change the index scan to index seek.
For example: create or modify one nonclustered index on table [TradeCodes] with two columns PartID and CodeTypeID.
Update statistics.

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

First of all, stop using NOLOCK all over the place. You want you queries to return correct results, don't you?

It was nice of you to include test data. Unfortunately, CodeTypeId is NULL for all rows in #TempPlAndCodeType and #AllPlData, so it was nothing I could work with.

Here is a rewrite of the query:

; WITH u AS (
     SELECT NULL AS PLID, tr.CodeTypeID
     FROM   Parts.TradeCodes tr 
     WHERE  EXISTS (SELECT *
                    FROM   #TempPlAndCodeType c 
                    WHERE  c.CodeTypeId = tr.CodeTypeID)
     UNION ALL
     SELECT pt.PLID, tr.CodeTypeID
     FROM   Parts.TradeCodes tr 
     JOIN   #AllPlData pt  ON pt.PartId = tr.PartID
     JOIN   #TempPlAndCodeType c ON c.CodeTypeId = tr.CodeTypeID 
                                AND pt.PLID=c.PLID
)
SELECT PLID, CodeTypeID, COUNT(*) AS [#partsHasCode]
INTO   #partHasCodes
FROM   u
GROUP  BY PLID, CodeTypeID

It's a fairly aggressive rewrite, so you will need to test that you get the correct result. But the key is to get rid of the OR condition in your original query.

For this query to perform well, you also need some indexes:

CREATE INDEX CodeTypeId_ix ON Parts.TradeCode(CodeTypeId) INCLUDE (PartId)
CREATE INDEX PartId_ix ON #AllPlData(PartId)

Share via

How to write this dashboard with another way to avoid slow running ?

1 additional answer

Your answer