May 31, 2021 at 2:54 pm
I work on sql server 2012 I face issue this query when run is very slow so how o enhance it
to be more faster
query and execution plan exist on link below :
https://www.brentozar.com/pastetheplan/?id=S1wEKwf5O
sql query as below ;
;WITH cte AS
(
SELECT
Po.GlobalPnId ,
Po.FamilyId,
po.CompanyID,
Po2.GroupId,
CAST( CONCAT(LTRIM(RTRIM(CASE WHEN Po.PortionKey=N'Blank' THEN ''
WHEN CHARINDEX('[', PO.PortionKey) >0 then replace(PO.PortionKey,N'[',N'[[')
ELSE Po.PortionKey END))
,LTRIM(RTRIM(CASE WHEN Po2.PortionKey=N'Blank' THEN ''
WHEN Po2.PortionKey LIKE '%[_]%' THEN SUBSTRING(Po.PortionKey, LEN(LTRIM(RTRIM(Po.PortionKey)))+1,LEN(LTRIM(RTRIM(Po2.PortionKey))))
WHEN CHARINDEX('[', PO2.PortionKey) >0 then LTRIM(RTRIM(replace(PO2.PortionKey,N'[',N'[[')))
ELSE Po2.PortionKey END)) )
AS NVARCHAR(200))PortionKey
, CAST( CONCAT(LTRIM(RTRIM(CASE WHEN PNK.PortionKey=N'Blank' THEN ''
WHEN CHARINDEX('[', PO.PortionKey) >0 then replace(PO.PortionKey,N'[',N'[[')
ELSE Po.PortionKey END))
,LTRIM(RTRIM(CASE WHEN PNK.PortionKey=N'Blank' THEN ''
WHEN PNK.PortionKey LIKE '%[_]%' THEN SUBSTRING(Po.PortionKey, LEN(LTRIM(RTRIM(Po.PortionKey)))+1,LEN(LTRIM(RTRIM(PNK.PortionKey))))
WHEN CHARINDEX('[', PNK.PortionKey) >0 then LTRIM(RTRIM(replace(PNK.PortionKey,N'[',N'[[')))
ELSE PNK.PortionKey END)) )
AS NVARCHAR(200)) PartNumber
FROM
extractreports.dbo.GetFinalResult Po WITH(NOLOCK)
INNER JOIN extractreports.dbo.GetFinalResult Po2 WITH(NOLOCK) ON Po.GlobalPnId = Po2.GlobalPnId And Po.GroupId = 1 AND Po2.GroupId = 2
INNER JOIN extractreports.dbo.GetFinalResult_K PNK WITH(NOLOCK) ON Po.GlobalPnId = PNK.GlobalPnId And Po.GroupId = 1 AND PNK.GroupId = 2
WHERE
RTRIM( Po.PortionKey) <> '' AND RTRIM( Po2.PortionKey) <> ''
AND Po2.PortionKey NOT LIKE '%[_]'
and Po.companyid=@CompanyId
UNION ALL
SELECT
t.GlobalPnId ,
t.FamilyId,
t.CompanyID,
Po2.GroupId,
CAST(CONCAT(t.PortionKey
,LTRIM(RTRIM(CASE WHEN Po2.PortionKey=N'Blank' THEN ''
WHEN CHARINDEX('[', PO2.PortionKey) >0 then replace(PO2.PortionKey,N'[',N'[[')
ELSE Po2.PortionKey End ))
) AS NVARCHAR(200)) PortionKey
, CAST(CONCAT(t.PortionKey
,LTRIM(RTRIM(CASE WHEN PNK.PortionKey=N'Blank' THEN ''
WHEN CHARINDEX('[', PNK.PortionKey) >0 then replace(PNK.PortionKey,N'[',N'[[')
ELSE PNK.PortionKey End ))
) AS NVARCHAR(200)) PartNumber
FROM CTE t
INNER JOIN extractreports.dbo.GetFinalResult Po2 WITH(NOLOCK) ON Po2.GlobalPnId = t.GlobalPnId AND Po2.GroupId = t.GroupId+ 1
INNER JOIN extractreports.dbo.GetFinalResult_K PNK WITH(NOLOCK) ON PNK.GlobalPnId = t.GlobalPnId AND PNK.GroupId = t.GroupId+ 1
WHERE t.companyid=@CompanyId
AND RTRIM( t.PortionKey) <> '' AND RTRIM( Po2.PortionKey) <> ''
)
select * ,(Select Max(GroupId) from cte c2 Where c2.FamilyId=c1.FamilyId ) MX into extractreports.dbo.getfinalmask from cte c1
so how to enhance it to be more faster
it take on 1000 rows too much time may be reach to two hour
and
script ddl and data dml
exist here below :
https://www.mediafire.com/file/hz74ca3z08xiic8/getscriptfinalresult.sql/file
June 1, 2021 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 1, 2021 at 4:48 pm
This should speed the query up
IF OBJECT_ID('tempdb..#CTE','U') IS NULL
DROP TABLE #CTE
SELECT *
INTO #CTE
FROM (SELECT Po.GlobalPnId ,
Po.FamilyId,
po.CompanyID,
Po2.GroupId,
CAST(CONCAT(LTRIM(RTRIM(CASE WHEN Po.PortionKey=N'Blank' THEN ''
WHEN CHARINDEX('[', PO.PortionKey) >0 then replace(PO.PortionKey,N'[',N'[[')
ELSE Po.PortionKey
END))
,LTRIM(RTRIM(CASE WHEN Po2.PortionKey=N'Blank' THEN ''
WHEN Po2.PortionKey LIKE '%[_]%' THEN SUBSTRING(Po.PortionKey, LEN(LTRIM(RTRIM(Po.PortionKey)))+1,LEN(LTRIM(RTRIM(Po2.PortionKey))))
WHEN CHARINDEX('[', PO2.PortionKey) >0 then LTRIM(RTRIM(replace(PO2.PortionKey,N'[',N'[[')))
ELSE Po2.PortionKey END)) )
AS NVARCHAR(200))PortionKey
, CAST( CONCAT(LTRIM(RTRIM(CASE WHEN PNK.PortionKey=N'Blank' THEN ''
WHEN CHARINDEX('[', PO.PortionKey) >0 then replace(PO.PortionKey,N'[',N'[[')
ELSE Po.PortionKey END))
,LTRIM(RTRIM(CASE WHEN PNK.PortionKey=N'Blank' THEN ''
WHEN PNK.PortionKey LIKE '%[_]%' THEN SUBSTRING(Po.PortionKey, LEN(LTRIM(RTRIM(Po.PortionKey)))+1,LEN(LTRIM(RTRIM(PNK.PortionKey))))
WHEN CHARINDEX('[', PNK.PortionKey) >0 then LTRIM(RTRIM(replace(PNK.PortionKey,N'[',N'[[')))
ELSE PNK.PortionKey END)) )
AS NVARCHAR(200)) PartNumber
FROM extractreports.dbo.GetFinalResult Po WITH(NOLOCK)
INNER JOIN extractreports.dbo.GetFinalResult Po2 WITH(NOLOCK)
ON Po.GlobalPnId = Po2.GlobalPnId
AND Po2.GroupId = 2
INNER JOIN extractreports.dbo.GetFinalResult_K PNK WITH(NOLOCK)
ON Po.GlobalPnId = PNK.GlobalPnId
AND PNK.GroupId = 2
AND Po2.PortionKey NOT LIKE '%[_]'
WHERE RTRIM( Po.PortionKey) <> ''
AND RTRIM( Po2.PortionKey) <> ''
AND Po.GroupId = 1
AND Po.companyid=@CompanyId
UNION ALL
SELECT t.GlobalPnId ,
t.FamilyId,
t.CompanyID,
Po2.GroupId,
CAST(CONCAT(t.PortionKey
,LTRIM(RTRIM(CASE WHEN Po2.PortionKey=N'Blank' THEN ''
WHEN CHARINDEX('[', PO2.PortionKey) >0 then replace(PO2.PortionKey,N'[',N'[[')
ELSE Po2.PortionKey End ))
) AS NVARCHAR(200)) PortionKey
, CAST(CONCAT(t.PortionKey
,LTRIM(RTRIM(CASE WHEN PNK.PortionKey=N'Blank' THEN ''
WHEN CHARINDEX('[', PNK.PortionKey) >0 then replace(PNK.PortionKey,N'[',N'[[')
ELSE PNK.PortionKey End ))
) AS NVARCHAR(200)) PartNumber
FROM CTE t
INNER JOIN extractreports.dbo.GetFinalResult Po2 WITH(NOLOCK)
ON Po2.GlobalPnId = t.GlobalPnId
AND Po2.GroupId = t.GroupId+ 1
INNER JOIN extractreports.dbo.GetFinalResult_K PNK WITH(NOLOCK)
ON PNK.GlobalPnId = t.GlobalPnId
AND PNK.GroupId = t.GroupId+ 1
WHERE t.companyid = @CompanyId
AND RTRIM( t.PortionKey) <> ''
AND RTRIM( Po2.PortionKey) <> ''
) x
CREATE CLUSTERED INDEX IX_#CTE_1 ON #CTE(FamilyId, GroupId DESC)
select *,
(SELECT Max(GroupId)
FROM #CTE c2
WHERE c2.FamilyId = c1.FamilyId) MX
into extractreports.dbo.getfinalmask
from #CTE c1 Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply