Indexing a view with an averaged column

Question

With Sql-Server, I create this view:

IF OBJECT_ID ('dbo.v_table', 'view') IS NOT NULL
   DROP VIEW [dbo].[v_table];
GO

CREATE VIEW [dbo].[v_table] 
WITH SCHEMABINDING
AS
select  toplevelcom_nr, avg(price_change), count(toplevelcom_nr) from dbo.table
group by toplevelcom_nr

The underlying table has 11+ million records and the view groups the column to 12 distinct values. Generally, the select query that needs to retrieve data looks the following:

select * from [dbo].[v_table]  where toplevelcom_nr = '12'

Since this query is rather slugghish, I need to gain vast performance increases: It takes around 5-10 sec, ideally would be < 1 sec.

When then trying to set an index on the view it did not seem to work for averaging-columns.

Go
CREATE UNIQUE CLUSTERED INDEX idx_v_table
   ON [dbo].[v_table]  (toplevelcom_nr);
GO

resulted in:

Msg 10125, Level 16, State 1, Line 12 Cannot create index on view "local.dbo.v_table" because it uses aggregate "AVG". Consider eliminating the aggregate, not indexing the view, or using alternate aggregates. For example, for AVG substitute SUM and COUNT_BIG, or for COUNT, substitute COUNT_BIG.

Coming to my Question:

Is there a smarter solution to increase the performance? Do I have to persist the table to really leverage performance if indexed views do not work?

Answer 1

Aside from following the suggestion of the error message you received (which is a limitation of indexed views) to utilize the SUM() and COUNT_BIG() functions to calculate the average instead, you can also try a columnstore index on your table.

I've had a lot of success with columnstore indexes on large tables that I needed to run analytical queries against such as aggregations. Perhaps an example of one you could try is:

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Table_toplevelcom_nr_price_change ON Table (toplevelcom_nr, price_change)

If you want to stick to an indexed view then you'd have to use SUM() and COUNT_BIG() and materialize them first in an indexed view, then do the calculation between them in a separate regular view.

Answer 2

To simulate AVG in an indexed view, you need to use SUM and COUNT_BIG, which are the only two aggregates allowed. This is already clear from the error message,

However, the actual calculation is not permitted to be in the view.

So instead you can create another non-indexed view that calculates it. We can also take this opportunity to force a NOEXPAND hint, which should nearly always be used.

CREATE VIEW [dbo].[v_table_Indexed] 
WITH SCHEMABINDING
AS
SELECT
  t.toplevelcom_nr,
  SUM(t.price_change) AS price_change,
  COUNT_BIG(*) AS count
FROM dbo.[table] t
GROUP BY t.toplevelcom_nr;

GO

CREATE UNIQUE CLUSTERED INDEX idx_v_table
   ON [dbo].[v_table_Indexed] (toplevelcom_nr);

GO

CREATE VIEW [dbo].[v_table]
AS
SELECT
  v.toplevelcom_nr,
  v.price_change,
  v.count,
  v.price_change / v.count AS avg
FROM v_table_Indexed v WITH (NOEXPAND);

GO

You then only select from the non-indexed v_table, which in turn refers to the indexed view.