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.
THIS POSTING IS PROVIDED “AS IS” WITH NO WARRANTIES, AND CONFERS NO RIGHTS
sounds all nice in theory and looks like an solid approach, but cloud patterns may also consider the economics based on the underlyings platform pricing. Viewing an item is a single storage transaction – applying your pattern just to view topmost items, and to workaround some *missing* functionality in table storage, implies 5 more storage transaction thus increasing costs to be six times as much and making SQL (Azure) again more reasonable
Agree, that the cost needs to be taken into account now. But also for the cloud you really need to think about scaling out.
Do keep in mind there are complexities scaling out on SQL Azure. If you think about putting the same app into SQL Azure, it’s unlikely you are going to want it in a single database, maybe for size, but almost certainly for throughput. Keep in mind that you are in a shared environment, and NOT running on a 48 core box with 15,000 rpm drives and 1/2 GB of RAM. – perhaps I need a post on this
Disagree that we are working around *missing* functionality.