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:
The Top 10 Downloaded bits of art work of the month
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