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

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

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.