Notifications
Clear all

Reporting view performance with business date and load date combination  


Posts: 74
(@adarsh)
Trusted Member
Joined: 8 months ago

My report views contains joins to multiple satellite tables and each satellite table has a version filter like

CREATE VIEW V_ME_SALES AS 

SEL report_dt.month_end, 
       sat_table_1.columns, 
       sat_table_2.columns 
FROM   sat_table_1
JOIN   calendar Report_dt 
ON     1=1 
JOIN 
       ( 
                SEL   cal.month_end, 
                         hkey_1, 
                         ldts row_number() OVER (partition BY cal.month_end,hkey ORDER BY applied_date DESC, load_date_utc DESC) rnum
                FROM     sat_table_1 
                JOIN     calendar cal 
                ON       sat_table_1.applied_date <=cal.month_end) v_sat_table_1 
ON     sat_table_1.hkey_1=v_sat_table_1.hkey_1 
AND    sat_table_1.ldts=v_sat_table_1.ldts 
AND    v_sat_table_1.rnum=1 and v_sat_table_1.month_end=report_dt.month_end
JOIN   sat_table_2 
JOIN 
       ( 
                SEL   cal.month_end, 
                         hkey_2, 
                         ldts row_number() OVER (partition BY cal.month_end,hkey ORDER BY applied_date DESC, load_date_utc DESC) rnum
                FROM     sat_table_2 
                JOIN     calendar cal 
                ON       sat_table_2.applied_date <=cal.month_end) v_sat_table_2 
WHERE  sat_table_2.hkey_2=v_sat_table_2.hkey_2 
AND    sat_table_2.ldts=v_sat_table_2.ldts 
AND    v_sat_table_2.rnum=1 and v_sat_table_2.month_end=report_dt.month_end

....

CALENDAR = contains only month ends.

These joins perform very slow as the inner query always has to join with all dates from calendar table and then filter the data for a particular date based on the where condition supplied by the user

select * from V_ME_SALES where month_end ='31 dec 2019';

 

Any suggestions on how to improve the performance?

 

Reply
Topic Tags
9 Replies