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

Transact-SQL
, ,
1

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

so how to enhance it to be more faster

it take on 1000 rows too much time may be reach to one hour

2

hi ahmed

nice .. first time i am seeing ... Paste The Plan

See in the plan where its taking a lot of time .. first step identify
Which Step .. Percentage ?

Second Step .. How to Resolve ...You can google search .. plenty of ideas
Indexes ( if large data )
small data joins filter first ..

Mine is just VERY GENERAL ADVICE ..

Good luck

Heavy Weights .. will be seeing hopefully they help you :+1:

3

this is script data
slow performance and run

4

can any one help me please
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
5

what is going on here?

  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

what is the difference between all of these GetFinalResult ?

6

on first part of union all
i try only to display group id =1

7

Hi

Please break it up into small parts

Then join the small parts together

Instead of 100 at one time
10 10 20 .....