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:

  1. has with schemabinding, in turn means have use two-part names reference table(s).
  2. has use count_big() in order able use group by clause
  3. doesn't directly contain average (indeed, avg isn't allowed in indexed views), however, can compute dividing sum() count_big().
  4. 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

Popular posts from this blog

html5 - What is breaking my page when printing? -

c# - must be a non-abstract type with a public parameterless constructor in redis -

ajax - PHP/JSON Login script (Twitter style) not setting sessions -