2

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 information
  • User - PK User_ID, stores user information
  • Rating - 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!

  • Are you using a SQL Server Scalar-Valued function? – JeffO Dec 15 '14 at 0:42
  • @JeffO haven't implemented the specifics as of yet as I'm still trying to find the best approach. The linear regression function is based on this approach – globetrotter Dec 15 '14 at 0:49
  • I think performing a linear regression on the overall average rating is more complex than necessary to perform this function. A movie's rating will trend upwards over a given period proportionally to ((average rating over period) - (previous average)) * (number of new ratings) / (total number of ratings). Have you tried something like this already? – Jules Dec 15 '14 at 11:26
  • @Jules I haven't but I'm more than open to suggestions if LR could be an overkill, could you elaborate further on this (possibly as an answer)? – globetrotter Dec 15 '14 at 11:38
  • I'm not sure there's anything more to say. I just think it's an implementation that's worth trying, should be a little easier to calculate, and may give you good results. Failing that, I've added an answer as to how I'd approach the regression problem. – Jules Dec 15 '14 at 11:44
2

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.

  • thanks @Jules, I'll definitely have a look at your solution. I can't upvote you for now (reputation doesn't allow me to do so) – globetrotter Dec 15 '14 at 12:46
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.