1

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?

4
  • Why not calculate the average the way the error message suggests? You would be allowed to index the view if you utilized only the SUM() and COUNT_BIG() functions to calculate the average instead. This is a limitation of indexed views.
    – J.D.
    Nov 4, 2021 at 11:44
  • Have you tried the solution suggested in the error message (e.g. (sum(price_change)/count_big(toplevelcom_nr) AS price_change, count_big(toplevelcom_nr) AS toplevelcom_nr_count)?
    – Dan Guzman
    Nov 4, 2021 at 11:47
  • Did that, but it does not solve the issue unfortunately (see chat below)
    – Bennimi
    Nov 4, 2021 at 14:36
  • See SO - indexed view with division
    – SMor
    Nov 4, 2021 at 14:49

2 Answers 2

3

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.

5
  • Thanks, I tried this already and got another error so I thought it´s a dead end: Cannot create the clustered index 'idx_v_table' on view 'local.dbo.v_table' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list. PS: I changed COUNT_BIG(toplevelcom_nr) to COUNT_BIG(*)
    – Bennimi
    Nov 4, 2021 at 14:15
  • @Bennimi Did you also change SUM(price_change) / COUNT_BIG(toplevelcom_nr) to SUM(price_change) / COUNT_BIG(*)? Indexed views are great under the right circumstances but unfortunately have a lot of restrictions (the new error you received above being another restriction I personally forgot about). If you're still stuck after that, maybe try the columnstore index instead?...less code to maintain and supports more use cases than just the one you've presented but should also make your use case really quick.
    – J.D.
    Nov 4, 2021 at 14:27
  • 1
    Yes did that as well. Yea, I think your right, it seem to much of a hassle. Thanks anyway and I will get my head around columnstore idx.
    – Bennimi
    Nov 4, 2021 at 14:37
  • 1
    Just tested columnstore idx and the performance boost is massive!!! thanks mate!
    – Bennimi
    Nov 4, 2021 at 15:49
  • @Bennimi No problem! They are pretty sweet for certain performance scenarios! Not a requirement, but feel free to accept my answer if you think it'll be helpful for others as well. Thanks!
    – J.D.
    Nov 4, 2021 at 16:18
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.