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?