I mentioned yesterday about how we use some trickery to maintain the top views table in Bid Now.
Items are stored in a table, but to generate a quick top views view, we maintain a separate table. This is done using the Task/Queue/Task pattern described yesterday here.
Since TOP isn’t a keyword that Windows Azure table storage deals with, we have to use some trickery.
Items are stored in the AuctionItems table. But when you visit the home page, you get several different lists of items. Each of these items are in fact separate tables, which are kept up to date using the Task/Queue/Task pattern.
For the Most Viewed list – we use the MostViewedItems table. The table contains PartitionKey, RowKey, TimeStamp, Title, EndDate, ItemId, PhotoUrl, ShortDescription & ThumbnailUrl. In fact just enough information to display the list and enable a click through to the item details.
The query to return the top 5 items is simple – we simply return the first 5 items from that table – which is super fast. How you say. Well we use the PartitonKey to order the table!
Take a look at the Most Viewed from the home page.
Here is the MostViewItems table. Note the partition key is numerical. Every time an item is viewed we read the row, decrement the number in the partition key, save the new row and delete the old one. (since you cannot update the partition key).
Note Football is 2nd from the bottom. If we view football, the update works and we end up with this on the home page.
And an update to the partition key. Note the number has been decremented by one.
You can use this pattern to keep your own data de-normalized and provide super fast queries.