Calculating monthly reports or statistics - MVC Entity Framework LinQ

Question

Hi

I am using MVC (C#) and the Entity Framework. I have a sql server database with tables called Profile, Gallery, ArtWork :

PROFILE: UserName, FirstName, etc...

GALLERY: GalleryID, UserName, GalleryName,

ARTWORK: ImageGuid, GalleryID, Description, PublishDate, Views, Downloads,

I have the following relationship between the tables:

Profile->Gallery->ArtWork (Foreign keys in bold above)

What I would like to know is the most efficient way to aquire the following for the stats page:

1. The Top 10 Downloaded bits of art work of the month

2. The most Downloaded designer of the month.

Its easy enough to construct the LinQ for the above but I don't want to be hitting the database everytime a user views the stats page. There could be 30,000+ designers having 5000+ bits of artwork each, and i dont want these results being calculated everytime someone hits the stats page. I only want to hit the database daily for stat 1, and monthly for stat 2.

Am I best using a view for each stat in the database and hitting that all the time, or would it be best setting a timer to hit the database daily and monthly (if so how please?), or is there a better way.

Thanks In Advance

Answer 1

I would store the query result in the application Cache. If you do this it doesn't matter so much how you construct the query, since it will only be run once until the cached item expires.