0

issue:

  • Update & query (using Power BI) of my Sales Weekly View takes forever (>30min) and I would like to understand how I could improve it

Current setup:

  • Sales Daily View: View containing aggregation and joins of two main sales tables and a date table
  • Sales Weekly View: View joining the "Sales Daily View", a sales table and two dimension tables and containing extensive logic (mainly aggregations)

Please let me know if you have any idea how to streamline this and improve the performance of the view.

Kevin Conseil
  • 11
  • 3

1 Answers1

0
  1. As @Tim mentioned the view will have the same performance as the underlying query. so check if you can optimize the Select query in the view.
  2. You could try using Common table expression (CTEs) for some aggregated results in the view instead of calculating in the select itself and then join the main query with the CTE.
  3. You can try using a materialized view if you have the capability to refresh it every day/ week based on your need and then create indexes on the materialized view and use it as a source in your power BI.
VTi
  • 1,309
  • 6
  • 14
  • Thanks for your answers. 1& 2: I already use CTE for all the aggregation and use a final select statement at the end. 3: The view is updated every week, therefore it might make sense to investigate the indexes option – Kevin Conseil May 29 '20 at 13:38
  • Let me know how it goes. – VTi May 29 '20 at 14:16
  • it seems that materialized views cannot be used in my case as it is: - based on another view - contains many outer joins - contains DISTINCT statements Do you have any other recommendations? – Kevin Conseil Jun 04 '20 at 12:41
  • Did you check the indexes ? Also it might be worth looking at the execution plan. I can't really say until I look at the View definition and structure of underlying tables. https://learn.microsoft.com/en-us/sql/relational-databases/performance/execution-plans?view=sql-server-ver15 – VTi Jun 04 '20 at 14:40
  • from my understanding (based on this post [link](https://stackoverflow.com/questions/3986366/how-to-create-materialized-views-in-sql-server), the way to materialize a view is to create an indexed view which comport several strict requirements such as: - not being a view of a view - not having distinct statements - not having outer joins – Kevin Conseil Jun 05 '20 at 07:24
  • here is the code: https://stackoverflow.com/questions/62212801/how-to-improve-a-slow-performing-sql-view-created-on-top-of-another-view – Kevin Conseil Jun 05 '20 at 10:04