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

  • Question

  • User696604810 posted

    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

    Monday, May 31, 2021 2:46 PM

All replies

  • User-1330468790 posted

    Hi ahmedbarbary, 

     

    According to the query plan you posted, there are some Table Scans for [GetFinalResult].[Po2], [GetFinalResult].[PNK] and [GetFinalResult].[PO]. You can improve your query by creating indexes.

    Referenece: 

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15

      

    Then we can check query plan after you adding indexes to see how we can do further enhancement.

      

    Best regards,

    Sean

    Tuesday, June 1, 2021 5:54 AM
  • User696604810 posted

    make indexes on which fields please 

    Tuesday, June 1, 2021 7:22 AM
  • User753101303 posted

    Hi,

    Should be on columns used in your where clause such as compaynid. Using a function such as RTRIM could prevent the use of an index, I would trim that before if applicable. Also in my approach is often to comment/rebuitl my query and check its performance and plan along the way one of the first thing too look for being likely table scan on tables in which you want to select a significant lower number of rows than stored...

    Tuesday, June 1, 2021 8:00 AM