I’m building an MVC .NET website where people can rate and review movies (similar to IMDb). I’m using SQL Server for the back-end.
I’ll be implementing a feature where the homepage displays trending movies, i.e. movies which have been positively rated within a particular time-period (let’s say last 7 days).
I’ll be using a stored procedure which implements linear regression to accomplish that.
My relevant SQL tables are the following:
Movie– PK Movie_ID, stores movie informationUser– PK User_ID, stores user informationRating– stores rating instances in the form of:
PK Rating_ID | FK Movie_ID | FK User_ID | RatingNo | Updated_Dt
1 5 10 7 15/12/2014 16:04:24
The above row would translate to “User with ID 10 gave movie with ID 5 a rating of 7/10 on 15/12/2014 16:04:24”.
I also have a function to calculate the average score of a movie in the form of AVG_Rate = sum of all ratings (for a given movie) / count of ratings (for a given movie).
My question is this: Given the provided table structure above, what would be the best way to implement “movies trending this week”? I can think of two ways at the moment:
- Create a permanent table to run linear regression on:
Movie_ID | AVG_Rate | Updated_Dt
Whenever a new rating row is added in Rating, also insert a new row in the new table, but instead of storing the actual rating, store the new calculated AVG_Rate instead:
Movie_ID | AVG_Rate | Updated_Dt
1 7.8 15/12/14 17:00:00 1 7.6 15/12/14 17:01:00 1 7.9 15/12/14 17:03:00
…And so on. I can then use the new table to check for linear regression of a given movie. In terms of the table becoming too large over a period of time, I can have the table only hold 7 days worth of data at any given time (by e.g. backing up every 7 days and deleting data older than a week).
- Create a temporary table to run linear regression on:
Same logic as above, except that the table is created temporarily on the fly whenever the stored procedure for getting trending movies is invoked. The AVG_Rate changes for each movie can be calculated directly from the Rating table.
I’m skeptical about which approach would work the best (I’m not advanced in SQL) and also not sure whether any of these two solutions are the way to go.
Any help & guidance would be greatly appreciated. Thanks!
5
I would probably do this a third way, which is by adding a column to your rating table whose value is the average rating after that rating is applied. This means that this stage of the calculation will only need to be preformed once rather than every time your stored procedure is executed, resulting in somewhat better performance. Technically this results in your database not being entirely normalized, but as it is only historical data that should not be changing that is denormalized I wouldn’t consider that huge issue.
This is similar to your first suggestion, of course, but avoids the duplication of information to a separate table.
1
I believe this is a perfect use case for a view, in other words a “virtual table” defined by an SQL query on real tables in the database.
By default a view is generated from the real data every time a query needs to be executed on it, but some RDMSs support views with caching/precomputing/etc. Oracle calls these “materialized views”, and SQL Server calls them “indexed views”.
To get a little more specific, Improving Performance with SQL Server 2008 Indexed Views says that:
Indexed views can increase query performance in the following ways:
- Aggregations can be precomputed and stored in the index to minimize
expensive computations during query execution.- Tables can be prejoined and the resulting data set stored.
- Combinations of joins or aggregations can be stored.
You may not need any of the huge performance improvements they claim, but this gives us a concrete idea of what SQL Server’s indexed views are capable of, so I think it’s fair to assert that an indexed view is functionally equivalent to your option #1 of creating an additional table, while a non-indexed view is functionally equivalent to your option #2 of creating a temporary table every time. Except that it’s probably much easier to switch between a regular view and an indexed view than it is to switch between a permanent table and a temporary table, so at the very least you’ll probably save yourself a some time experimenting with your options this way.
This SO answer has some more helpful links on the subject.