Sort in query plan TSQL

Question

Need to handle query by eliminating and improving performance by deleting sort operators which consumes the greatest amount of resources.

The temp table is around 20,000 rows and the physical table is around 60 million of rows.

I am using LAG function due to that I need to compare values in bigger table, Have You guys any idea to figure it out ?

I am posting query, but if you will need any further info then let me know.

;WITH CTE AS 
(
    SELECT  
        a.VIN_NUMBER,
        B.CELL_VALUE, B.CELL_VALUE_NEGATIVE_VALUES,
        ROW_NUMBER() OVER (PARTITION BY B.VIN_NUMBER, B.LOG_NUM, B.SEQUENCE_NUM_OF_CELL 
                           ORDER BY B.VIN_NUMBER, B.DATE_OF_CELL_READ, B.LOG_NUM, B.SEQUENCE_NUM_OF_CELL) ROW_NUM,
        B.CELL_VALUE - LAG(B.CELL_VALUE, 1) OVER (ORDER BY B.VIN_NUMBER, B.DATE_OF_CELL_READ, B.LOG_NUM, B.SEQUENCE_NUM_OF_CELL) CELL_VALUE_NEW 
    FROM 
        #TEMP_CHASSI_LAST_LOAD A
    JOIN 
        DBO.LOGS_FROM_CARS B WITH (NOLOCK) ON B.ROW_CREATION_DATE BETWEEN A.MIN_ROW_CREATION_DATE 
                                           AND A.MAX_ROW_CREATION_DATE 
                                           AND A.VIN_NUMBER = B.VIN_NUMBER
)            
SELECT 
    VIN_NUMBER,
    IIF(CELL_VALUE_NEW < 0, 0, CELL_VALUE_NEW) AS CELL_VALUE_NEW,
    IIF(CELL_VALUE_NEW < 0, CELL_VALUE_NEW, NULL) AS CELL_VALUE_NEGATIVE_VALUES
FROM 
    CTE 
WHERE 
    ROW_NUM > 1 
    AND (CELL_VALUE_NEW <> CELL_VALUE OR CELL_VALUE IS NULL) 

Answer 1

It's hard to be sure what you are doing without sample data and full execution plan, but I'd explore a few options.

First, I don't think your LAG() is correct. I think you should add PARTITION BY B.VIN_NUMBER. Pretty sure you do not want to compare values of different VIN's. This will let you get rid of your ROW_NUMBER() as LAG() will now have NULL for the first row. That means your CELL_VALUE_NEW <> CELL_VALUE will filter out, so can remove ROW_NUM > 1

Optimized Query

WITH CTE AS (
    SELECT  
        A.VIN_NUMBER,
        B.CELL_VALUE,
        B.CELL_VALUE_NEGATIVE_VALUES,
        B.CELL_VALUE - LAG(B.CELL_VALUE, 1) OVER (PARTITION BY B.VIN_NUMBER ORDER BY B.DATE_OF_CELL_READ, B.LOG_NUM, B.SEQUENCE_NUM_OF_CELL) CELL_VALUE_NEW 
    FROM #TEMP_CHASSI_LAST_LOAD AS A
    INNER JOIN dbo.LOGS_FROM_CARS B WITH (NOLOCK) 
        ON B.ROW_CREATION_DATE BETWEEN A.MIN_ROW_CREATION_DATE AND A.MAX_ROW_CREATION_DATE 
        AND A.VIN_NUMBER = B.VIN_NUMBER
)            
SELECT 
    VIN_NUMBER,
    IIF(CELL_VALUE_NEW < 0, 0, CELL_VALUE_NEW) AS CELL_VALUE_NEW,
    IIF(CELL_VALUE_NEW < 0, CELL_VALUE_NEW, NULL) AS CELL_VALUE_NEGATIVE_VALUES
FROM CTE 
WHERE (CELL_VALUE_NEW <> CELL_VALUE OR CELL_VALUE IS NULL) 

Things to Review:

• Double check data types for your join conditions. Ex. make sure MIN_ROW_CREATION_DATE and MAX_ROW_CREATION_DATE are the same as ROW_CREATION_DATE. Makes sure it's not text vs date. Ideally VIN_NUMBER is using CHAR(17) (all car VIN's are 17 characters)
• Create index on larger table (and maybe try one on the temp table. Query performance improvement might be worth the time to create the index on the temp table)

CREATE INDEX ix_test ON dbo.LOGS_FROM_CARS(VIN_NUMBER,ROW_CREATION_DATE) 
    INCLUDE (CELL_VALUE,CELL_VALUE_NEGATIVE_VALUES,DATE_OF_CELL_READ, LOG_NUM, SEQUENCE_NUM_OF_CELL)

• Try FORCESEEK option on table join to LOGS_FROM_CARS. Be cautious using query hints as can lead to issues down the road, but might be worth it for this query
• Are you sure you need CELL_VALUE_NEGATIVE_VALUES from LOGS_FROM_CARS? I don't see it used anywhere. Would remove that from the query if you don't need it