1

I have the following expensive query that runs against our data warehouse:

SELECT
    d.DateYear,
    COUNT_BIG(*) AS WidgetCount
FROM Widgets AS w
JOIN Dates AS d
    ON d.DateId = w.DateId
WHERE w.TypeId IN (1, 2)
GROUP BY
    d.DateYear

I've discovered that I can hugely increase the performance of this query with the following indexed view (see also: Using indexed views for aggregates - too good to be true?)

CREATE VIEW IX_Widgets
WITH SCHEMABINDING
AS
    SELECT
        TypeId,
        DateId,
        COUNT_BIG(*) AS WidgetCount
    FROM Widgets
    GROUP BY DateId
GO

CREATE UNIQUE CLUSTERED INDEX IX_Widgets ON IX_Widgets
(
    TypeId,
    DateId
)

The best bit is that SQL Server starts using this indexed view even though its not referenced in the original query - this is really nice for a data warehouse as it means that queries can be written against a well-defined reporting schema, and then supported by crazy (but super efficient) indexed views that do weird groups.

The frustrating thing is that as soon as I change the typeIds that I am filtering by or parameterise the TypeId values (e.g. WHERE w.TypeId IN (@typeId1, @typeId2, ...)) SQL Server stops using the indexed view and reverts back to a really slow query.

What decides when SQL Server will use an indexed view? I've experienced similar problems in the past where it is difficult to determine when SQL Server will and won't use an indexed view. It would be great to be able to use this without having to change my query to explicitly use this view, but only if I know with certainty that its going to perform.

Community
  • 1
Justin
  • 920
  • 1
  • 7
  • 11
  • Without actual, post-execution plans, and understanding the cardinality of those columns, hard to tell. Why don't you just change the queries to use the view instead of relying on auto-matching? – Aaron Bertrand Jan 05 '15 at 17:20
  • @AaronBertrand Thats my fallback plan, I just wanted to understand why it works only some of the time. – Justin Jan 05 '15 at 17:26
  • Probably to do with whether or not the text of the query changes and the cardinality of the values you're filtering on. The text would definitely change if you hard-code the values (depending on parameterization settings of the database), or if you use a different number of parameters. There are many reasons why you would get a different plan, why some plans use the indexed view and others don't, again, hard to tell without much more specific information. – Aaron Bertrand Jan 05 '15 at 18:19

0 Answers0