This query is very slow how to enhance it to be more faster?

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 too much time may be reach to one hour

and

script ddl and data dml
exist here below :
https://www.mediafire.com/file/hz74ca3z08xiic8/getscriptfinalresult.sql/file

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Salman Beg5y ago
You can put primary key, foreign key etc. See this link for details, https://dataschool.com/sql-optimization/how-indexing-works/
0
ahmed elbarbary5y ago
thank you for repl
tell me put indexing on which field
0
Salman Beg5y ago
Hey. Make sure you use the proper indexing. Also follow these article and get it improved, https://www.c-sharpcorner.com/UploadFile/ff2f08/tips-to-improve-sql-database-performance/ https://www.sqlshack.com/why-is-my-cte-so-slow/ https://stackoverflow.com/questions/8560596/improve-sql-cte-query-performance
0