We have a data warehouse with a fairly large record count (10-20 million rows) and often run queries that count records between certain dates, or count records with certain flags, e.g.
SELECT
f.IsFoo,
COUNT(*) AS WidgetCount
FROM Widgets AS w
JOIN Flags AS f
ON f.FlagId = w.FlagId
WHERE w.Date >= @startDate
GROUP BY f.IsFoo
The performance isn't awful, but can be relatively sluggish (perhaps 10 seconds on a cold cache).
Recently I discovered that I can use GROUP BY
in indexed views and so tried out something similar to the following
CREATE VIEW TestView
WITH SCHEMABINDING
AS
SELECT
Date,
FlagId,
COUNT_BIG(*) AS WidgetCount
FROM Widgets
GROUP BY Date, FlagId;
GO
CREATE UNIQUE CLUSTERED INDEX PK_TestView ON TestView
(
Date,
FlagId
);
As a result the performance of my first query is now < 100ms, and the resulting view & index is < 100k (although our row count is large, the range of dates and flag IDs means that this view only contains 1000-2000 rows).
I thought that perhaps this would criple the performance of writes to the Widget table, but no - the performance of inserts and updates into this table is pretty much unaffected as far as I could tell (plus, being a data warehouse this table is updated infrequently anyway)
To me, this seems way too good to be true - is it? What do I need to be careful with when using indexed views in this way?