Alternate to below query

gs79
gs79 used Ask the Experts™
on
Is there a better way to write this query: The performance of this query is very bad and is failing to complete sometimes..

The query is hitting the same table twice once for getting the metrics and once for getting the other information based  on the transaction_id. I think this could be one area to look into..

Another thing that could be causing the problem is use of "OR" operator..

PLease let me know if there is any other approach to re-write this..Even if there is a procedural approach..

Note: the sales table has close to 100 M records..

Here is the query..

Thanks

SELECT Q1.GCF AS GCF,
          Q1.BUYER_DW_ID AS BUYER_DW_ID,
          Q2.TRANSACTION_ID AS TRANSACTION_ID,
          Q2.EVENT_ID AS EVENT_ID,
          Q2.SRC_CREATED_DTTM_SALE AS SRC_CREATED_DTTM_SALE,
          Q2.EVENT_DATE_UTC AS EVENT_DATE_UTC,
          Q2.VENUE_ID AS VENUE_ID,
          Q2.GENRE_ID AS GENRE_ID,
          Q1.lifetime_cnt,
          Q1.lifetime_tspo
     FROM (
             SELECT  BUYER_DW_ID, MAX (SPF.TRANSACTION_ID) AS TRANSACTION_ID,   GENRE_CAT_FINAL AS GCF,
             COUNT(1) as LIFETIME_CNT, TRUNC(SUM(SPF.TICKET_COST)/COUNT(1),2) AS LIFETIME_TSPO
              FROM SALES SPF,  GENRE G
                WHERE  SPF.GENRE_DW_ID = G.GENRE_DW_ID
               AND BUYER_DW_ID IN (SELECT DISTINCT BUYER_DW_ID FROM SALES SPF, DW_LOAD_CONTROL_TBL L
               --AND spf.buyer_Dw_id in (39813868,339673096,339678210,339673084,83424024,160168940,273419585,67218308)      
               WHERE  L.MAPPING_NAME = 'DW_USERS_DIM_MAP_INC'
               AND (  (spf.SRC_CREATED_DTTM_CONFIRM >= L.START_DTTM   AND spf.SRC_CREATED_DTTM_CONFIRM < L.END_DTTM)
                           OR
                          (spf.SRC_CREATED_DTTM_CANCEL >= L.START_DTTM    AND spf.SRC_CREATED_DTTM_CANCEL < L.END_DTTM)
                       )
                    AND CONFIRM_DT_DW_ID > 0
                    AND CANCEL_DT_DW_ID = -999
           GROUP BY GENRE_CAT_FINAL, BUYER_DW_ID
          ) Q1,
          (SELECT F.TRANSACTION_ID,
                  E.EVENT_ID AS EVENT_ID,
                  F.SRC_CREATED_DTTM_SALE AS SRC_CREATED_DTTM_SALE,
                  E.EVENT_DATE_UTC AS EVENT_DATE_UTC,
                  E.VENUE_ID AS VENUE_ID,
                  G.GENRE_ID AS GENRE_ID
             FROM SALES F, EVENTS E, GENRE G
            WHERE     F.EVENT_DW_ID = E.EVENT_DW_ID
                  AND F.GENRE_DW_ID = G.GENRE_DW_ID
                  AND CONFIRM_DT_DW_ID > 0
                  AND CANCEL_DT_DW_ID = -999) Q2
    WHERE Q1.TRANSACTION_ID = Q2.TRANSACTION_ID
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Any ideas to better rewrite the above query..I tried using the index hints and though it eliminated FTS on one of the big tables. there was no significant gain in the performance..

Please help..kind of urgent..

Thanks
Top Expert 2012

Commented:
Since you are not using T-SQL you may want to request that the MS SQL Server topic area be removed as it does not apply.
Commented:
Dont know oracle but simplifying it using sql modify accordingly for oracle..

I think you make your query more complicated by joining same table multiple times again and again.....

1) STEP 1:=>> just store all buyer id inside some variable say @BUYER_DW_ID  in comma seperated form see below........................



Declare @BUYER_DW_ID as int(32)

SELECT @BUYER_DW_ID=Colease(BUYER_DW_ID+',','')  FROM SALES SPF, DW_LOAD_CONTROL_TBL L  WHERE  L.MAPPING_NAME = 'DW_USERS_DIM_MAP_INC'
               AND (  (spf.SRC_CREATED_DTTM_CONFIRM >= L.START_DTTM   AND spf.SRC_CREATED_DTTM_CONFIRM < L.END_DTTM)
                           OR
                          (spf.SRC_CREATED_DTTM_CANCEL >= L.START_DTTM    AND spf.SRC_CREATED_DTTM_CANCEL < L.END_DTTM)
                       )
                    AND CONFIRM_DT_DW_ID > 0
                    AND CANCEL_DT_DW_ID = -999
           GROUP BY BUYER_DW_ID



2) STEP 2:=>>  AS you are using just (SALES F,  GENRE G,EVENTS E)  tables only i had made it a single query and checking for byerid from our above created variable....



              SELECT  F.TRANSACTION_ID,
                  E.EVENT_ID AS EVENT_ID,
                  F.SRC_CREATED_DTTM_SALE AS SRC_CREATED_DTTM_SALE,
                  E.EVENT_DATE_UTC AS EVENT_DATE_UTC,
                  E.VENUE_ID AS VENUE_ID,
                  G.GENRE_ID AS GENRE_ID,
                  F.BUYER_DW_ID,
                  GENRE_CAT_FINAL AS GCF,
                   COUNT(1) as LIFETIME_CNT,
                  TRUNC(SUM(SPF.TICKET_COST)/COUNT(1),2) AS LIFETIME_TSPO                
                  FROM SALES F,  GENRE G,EVENTS E
                  WHERE  F.GENRE_DW_ID = G.GENRE_DW_ID
                     AND F.EVENT_DW_ID = E.EVENT_DW_ID
                     AND CONFIRM_DT_DW_ID > 0
                     AND CANCEL_DT_DW_ID = -999
                     AND BUYER_DW_ID IN  @BUYER_DW_ID
The subquery below is the one that is killing you ( there is no equijoin here, plus the OR operator, plus the DISTINCT keyword -If you have a chance, just remove all indexes to make it worse) :

SELECT DISTINCT BUYER_DW_ID FROM SALES SPF, DW_LOAD_CONTROL_TBL L                    
                                   WHERE  L.MAPPING_NAME = 'DW_USERS_DIM_MAP_INC'
                                   AND (  (spf.SRC_CREATED_DTTM_CONFIRM >= L.START_DTTM   AND spf.SRC_CREATED_DTTM_CONFIRM < L.END_DTTM)
                                               OR
                                              (spf.SRC_CREATED_DTTM_CANCEL >= L.START_DTTM    AND spf.SRC_CREATED_DTTM_CANCEL < L.END_DTTM)
                                           )
                                                      AND CONFIRM_DT_DW_ID > 0
                                                      AND CANCEL_DT_DW_ID = -999
                                             GROUP BY GENRE_CAT_FINAL, BUYER_DW_ID

Run the above query only and let us know how it performs.
Also:
- Post its explain plan.
- What columns are being accessed by the index used by Sales table?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial