SQLTeam.com | Weblogs | Forums

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

so how to enhance it to be more faster

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

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:

this is script data
slow performance and run

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

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 ?

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

Hi

Please break it up into small parts

Then join the small parts together

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