Storing expensive database calculations in Microsoft SQL server -
i'm using microsoft sql server. want store/cache expensive calculations can reused. in example have items rated users, database schema looks this:
user id : int item id : int useritemrating userid (user) itemid (item) rating : int
to highest rated items need calculate average rating of each item (lets assume operation expensive , useritemrating changed/updated frequently). way of caching calculation? should add column "averagerating" item , create trigger updates it? create view? temporary table holds calculations?
in sql server, create indexed view, this:
create view dbo.itemratings schemabinding select itemid, count_big(*) cnt, sum(rating) totalrating dbo.useritemrating group itemid go create unique clustered index ix_itemratings on dbo.itemratings (itemid)
there various restrictions on creation , usage of indexed views, above valid (assuming useritemrating
in dbo
schema).
things note:
- has
with schemabinding
, in turn means have use two-part names reference table(s). - has use
count_big()
in order able usegroup by
clause - doesn't directly contain average (indeed,
avg
isn't allowed in indexed views), however, can compute dividingsum()
count_big()
. - to make use of index on view, either need query
noexpand
hint, or running enterprise edition or better.
why preferred on trigger/table based solution? because code perform maintenance built in sql server, , have lower overall overhead other solution. (and don't have spend time making sure it's correct)
if using enterprise edition, can make use of indexed view without query directly referencing - such if make query against base table asked count
, sum
, or avg
, might use index.
Comments
Post a Comment