0

Let me just explain my problem with a simple example -

I have this table -

enter image description here

Now, this table is queried with a date range parameter. The result will be sum of value for each Type for that date range. Like if I query for From = "12/1/2016" and To = "12/2/2016", the result will be -

enter image description here

Now, Currently I am querying the table using via SSRS and then do the calculations IN SSRS and display. But this takes a lot of time since the dataset is very huge and there are lot of other calculations as well.

I want to have a materialized view which can have a snapshot of the data at midnight and pre-calculate all the aggregations, so that while querying using date range, the output will be much faster.

I can calculate the aggregations if only say for all the different Types and save in MV, But how do I do it for Dates?

Thanks in advance!

Prateek Singh
  • 863
  • 1
  • 8
  • 28
  • There is no way to use parameters in an MV. – sagi Dec 29 '16 at 08:37
  • 3
    You can create your MV as SELECT date, type, sum(value) from table GROUP by date, type, by doing this you are aggregating the value column by type. This newly created mv will contain lesser records as compared to your table and your performance will be better as you will now query from the mv. You can also consider partitioning the mv or creating an index on the date column of the mv or both. – phonetic_man Dec 29 '16 at 08:46
  • @sagi : I know, idea is to query MV with parameters :) – Prateek Singh Dec 29 '16 at 09:00
  • Listen to @phonetic_man idea. It should improve performance. – sagi Dec 29 '16 at 09:10
  • @phonetic_man Thanks for the pointers man. – Prateek Singh Dec 30 '16 at 01:43

0 Answers0