Question

Performance issue due full table scan

  • 10 July 2019
  • 0 replies
  • 13 views

Hi there!


I have a task to do a look that retrieve data and calculate measure for the one last month only



filters: {

field: demand_date

value: “this month”

}



, but this works so long.

My look retrieves all the data from table DEMAND_MART.DAILY_CORE_STATS_EXTENDED ,but I need only the latest month data. Is there any ability to restrict DEMAND_MART.DAILY_CORE_STATS_EXTENDED only but not the whole FROM section?


There is a part of source code:



FROM DEMAND_MART.DAILY_CORE_STATS_EXTENDED AS daily_core_stats

LEFT JOIN DIM.FLIGHT_MEDIA_DETAILS_BASE AS flight_media_details_base ON daily_core_stats.FLIGHT_MEDIA_ID = flight_media_details_base.FLIGHT_MEDIA_ID

LEFT JOIN DIM.FLIGHT_DETAILS AS flight_details ON flight_media_details_base.FLIGHT_ID = flight_details.FLIGHT_ID

FULL OUTER JOIN DIM.CAMPAIGN_DETAILS_BASE AS campaign_details_base ON flight_details.CAMPAIGN_ID = campaign_details_base.CAMPAIGN_ID


LEFT JOIN DIM.CUSTOMER_DETAILS AS customer_details ON campaign_details_base.CUSTOMER_ID = customer_details.CUSTOMER_ID

LEFT JOIN campaign_bookings ON campaign_bookings.CAMPAIGN_ID = campaign_details_base.CAMPAIGN_ID

LEFT JOIN DIM.INSERTION_ORDER_DETAILS AS insertion_order_details ON campaign_details_base.INSERTION_ORDER_ID = insertion_order_details.INSERTION_ORDER_ID

LEFT JOIN DIM.DEMAND_UNITS_BUDGET AS insertion_order_demand_units_budget ON insertion_order_demand_units_budget.INSERTION_ORDER_ID = insertion_order_details.INSERTION_ORDER_ID

and insertion_order_demand_units_budget.PL_PLAN_ID IS NULL

and insertion_order_demand_units_budget.CAMPAIGN_ID IS NULL

and insertion_order_demand_units_budget.FLIGHT_ID IS NULL

and insertion_order_demand_units_budget.FLIGHT_MEDIA_ID IS NULL


WHERE (NOT (insertion_order_details.INSERTION_ORDER_ID IS NULL)) AND TRUE AND (customer_details.COUNTRY_ID >= 0 OR customer_details.COUNTRY_ID IS NULL)

GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12

HAVING ((MIN(campaign_bookings.CAMPAIGN_START_DATE) < (DATEADD(‘month’, 3, DATE_TRUNC(‘month’, CURRENT_DATE()))))) AND ((MAX(campaign_bookings.CAMPAIGN_END_DATE) >= (DATEADD(‘month’, 0, DATE_TRUNC(‘month’, CURRENT_DATE()))))) AND ((CASE WHEN MAX(campaign_bookings.isfiller) THEN 1 ELSE 0 END

) = 0) AND ((CASE WHEN MAX(campaign_bookings.is_test_campaign) THEN 1 ELSE 0 END

) = 0) AND ((CASE WHEN MIN(campaign_bookings.io_campaign_status_finished_paused_ended) AND (COALESCE(SUM(CASE WHEN (((daily_core_stats.DEMAND_DATE ) >= ((DATE_TRUNC(‘month’, CURRENT_DATE()))) AND (daily_core_stats.DEMAND_DATE ) < ((DATEADD(‘month’, 1, DATE_TRUNC(‘month’, CURRENT_DATE())))))) THEN daily_core_stats.DEMAND_REVENUE ELSE NULL END), 0)) = 0 AND NOT MIN(campaign_bookings.io_format_type) THEN 1 ELSE 0 END

) = 0) AND ((CASE WHEN MIN(campaign_bookings.io_campaign_status_planning_reserved) THEN 1 ELSE 0 END

) = 0)

ORDER BY 6

LIMIT 5000



0 replies

Be the first to reply!

Reply