Indexed View not being used for Partitioned Clustered Columnstore Index

  • I am trying to use an indexed view to allow for aggregations to be generated more quickly in my test data warehouse. The Fact Table I am creating the indexed view on is a partitioned clustered columnstore index.

    I have created a view with the following code:

    ALTER view dbo.FactView

    with schemabinding

    as

    select local_date_key, meter_key, unit_key, read_type_key, sum(isnull(read_value,0)) as [s_read_value], sum(isnull(cost,0)) as [s_cost]

    , sum(isnull(easy_target_value,0)) as [s_easy_target_value], sum(isnull(hard_target_value,0)) as [s_hard_target_value]

    , sum(isnull(read_value,0)) as [a_read_value], sum(isnull(temperature,0)) as [a_temp], sum(isnull(co2,0)) as [s_co2]

    , sum(isnull(easy_target_co2,0)) as [s_easy_target_co2]

    , sum(isnull(hard_target_co2,0)) as [s_hard_target_co2], sum(isnull(temp1,0)) as [a_temp1], sum(isnull(temp2,0)) as [a_temp2]

    , sum(isnull(volume,0)) as [s_volume], count_big(*) as [freq]

    from dbo.FactConsumptionPart

    group by local_date_key, read_type_key, meter_key, unit_key

    I then created an index on the view as follows:

    create unique clustered index IDX_FV on factview (local_date_key, read_type_key, meter_key, unit_key)

    I then followed this up by running some large calculations that required use of the aggregation functionality on the main fact table, grouping by the clustered index columns and only returning averages and sums that are available in the view, but it still uses the underlying table to perform the aggregations, rather than the view I have created. Running an equivalent query on the view, then it takes 75% less time to query the indexed view directly, to using the fact table. I think the expected behaviour was that in SQL Server Enterprise or Developer edition (I am using developer edition), then the fact table should have used the indexed view. Can someone suggest what I might be missing, for the query not to be using the indexed view?

  • The optimizer won't always be able to spot that the indexed view provides all the columns needed for your data. This is especially true if the queries involved are beyond simple, as these must be. You may just have to write the queries directly to the view.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply