SQL Server Performance

Long running query

Discussion in 'T-SQL Performance Tuning for Developers' started by abhishek.goel123, Oct 28, 2008.

  1. abhishek.goel123 New Member

    Hi Guys,
    I have a query that is taking along time to complete.... and i am not able to figure out how to rewrite it. Can anybody help in this issue..?
    INSERT INTO tableA
    SELECT DISTINCT
    A.PROMOTION_ID,
    C.PRODUCT_UPC,
    CASE WHEN @YESTERDAYS_DATE = A.END_DATE THEN 'EXPIRED YESTERDAY'
    ELSE dbo.F_PROMOTION_STATUS(A.promotion_id, @TODAYS_DATE)
    END STATUS,
    B.GROUP_ID
    FROM Table_PROMOTION A INNER JOIN TEMP_HIERARCHY B ON
    A.PROMOTION_ID = B.PROMOTION_ID
    INNER JOIN Product_HIERARCHY_REF C ON
    B.PRODUCT_HIERARCHY = SUBSTRING(C.PRODUCT_HIERARCHY, 1, LEN(B.PRODUCT_HIERARCHY))
    WHERE
    (dbo.F_PROMOTION_STATUS(A.promotion_id, @TODAYS_DATE) IN ('LIVE', 'PENDING_START') OR
    A.END_DATE = @YESTERDAYS_DATE)
    AND A.PROMO_EVENT_UPC_ALL = 0
    AND LEFT(B.PRODUCT_HIERARCHY_LEVEL, 3) = 'GRD'
    AND C.PRODUCT_UPC NOT IN (SELECT B.UPC FROM T_LPM_PROMOTION A INNER JOIN #I207_T_LPM_PROMOTION_UPC B ON
    A.PROMOTION_ID = B.PROMOTION_ID
    INNER JOIN #I207_T_LPM_GRD_HIERARCHY_REF C ON
    B.UPC = C.PRODUCT_UPC
    WHERE
    (dbo.F_PROMOTION_STATUS(A.promotion_id, @TODAYS_DATE) IN ('LIVE', 'PENDING_START') OR
    A.END_DATE = @YESTERDAYS_DATE)
    AND A.PROMO_EVENT_UPC_ALL = 0
    AND LEFT(B.PRODUCT_HIERARCHY_LEVEL, 3) = 'GRD')
    IN the above query table TEMP_HIERARCHY contains the user selected HIERARCHY and the table Product_HIERARCHY_REF contains all the unique products and their Hierarchy's .
    Both the table TEMP_HIERARCHY and Product_HIERARCHY_REF are huge so its taking time to Join.
    Is there any other way to rewrite the query or replace the where clause...?
    Thanks in advance
    Abhishek
  2. madhuottapalam New Member

    It is better to start from Execution Plan. However, i would like to make few comments here. You are using Scalar function ( F_PROMOTION_STATUS) in the query in couple of places which has to be removed. Usage of functions like Substring() in ON Condition will eliminate index usage.
    Madhu
  3. Adriaan New Member

    You say " IN the above query table TEMP_HIERARCHY contains the user selected HIERARCHY and the table Product_HIERARCHY_REF contains all the unique products and their Hierarchy's ."
    You're actually JOINing on a SUBSTRING that starts at the first position, so you might try adding records to TEMP_HIERARCHY that each have a string that is one character shorter than the previous value.
    Then you can drop the SUBSTRING from the expression, although you should change that join into a subquery to avoid duplicates on the result set.

Share This Page