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?
SUM()
andCOUNT_BIG()
functions to calculate the average instead. This is a limitation of indexed views.sum(price_change)/count_big(toplevelcom_nr) AS price_change, count_big(toplevelcom_nr) AS toplevelcom_nr_count
)?