I've written a FB App and would like to add some more features and make it more social. One of the things I am trying to do is query table storage according to the users friends and discover whether the users friends have "watched" a video within my app. I can get the friend ids from FB fine, but I am not sure how to construct my query to table storage since the maximum number of friends a FB user can have is 5000 so potentially I could end up with a query like so:
var ts1 = azure.createTableService(config.storageAccount, config.storageAccessKey, config.tableHost);
var query = azure.TableQuery
.select()
.from('hits')
.where('PartitionKey eq ?', '0')
for (f in friends){
query.or('UserID eq ?', friends[f].UserID);
}
ts1.queryEntities(query, function (err, result){
etc ...
}
Resulting in one big fat query!
My question is whether this is the most efficient and cost effective (minimal table storage transactions) when doing queries of this nature against Windows Azure Table Storage or is their a better way?
Keep into account that Table Storage isn't a relational database, this means you can't leverage the power of stored procedures, built in functions like SUM etc... This means you can't do server-side calculations like counting the total hits for a single user and returning that result.
Instead with Table Storage you'll need to pull in all this data and to the work on the consumer side. A different solution is duplicating your data. It's fine to have a record for each hit with info like the video, time, ... But on the other hand, you'll need a different table containing the total hits for each user (like a shared counter).
That way, when you want the total hits for a single user you'll simply need to query the value, without having to recalculate it over and over again. You'll write 2 records instead of 1, but reading will be so much faster.
Steve Marx wrote a good introductory post about this: Architecting Scalable Counters with Windows Azure