locked
Azure Storage Table - Sort Friends algorithm RRS feed

  • Question

  • I have a table of sorted players by their scores which is updated frequently.

    I have another table for friend list (PK = playerID, RK = friend's playerID)

    I would like keep friends sorted by their scores too. Or simply get the top 10 friends with highest score. Is there an efficient way to do this which doesn't kill the system if there are millions of users.

    Monday, August 5, 2013 9:50 AM

Answers

  • Hi,

      >> Is there an efficient way to do this which doesn't kill the system if there are millions of users

    Table storage can be sorted by partition/row keys. If the scores are static, you can change your role key to be score + '_' + friend's ID. Then the entities will be sorted automatically by scores. But if the scores are dynamic (as in your case), then I have not had an optimal solution. However, a player usually will not have too many friends (even on Facebook a typical user may has less than 1000 contacts). So query the data in random order and then do an in memory sort would not take too much time.

    Best Regards,

    Ming Xu


    Ming Xu
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 6, 2013 1:05 AM
  • Hi,

      >> Then make 1000 query from the score tables before do an in- memory sorting

    It seems you don't need 1000 queries. A single query can return all friends data. Even if the scores are stored in a different table, you can use several queries with the "or" relationship (a single query may result in a very long URI, so several queries may be needed). If you use LINQ, you can also dynamically build an expression tree to mimic SQL's IN keyword as described in http://social.technet.microsoft.com/wiki/contents/articles/15698.where-in-and-not-in-queries-with-wcf-data-services.aspx. In addition, sorting 1000 items in memory is usually very fast (but maybe not 1000000 items).

    Best Regards,

    Ming Xu


    Ming Xu
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 7, 2013 2:09 PM

All replies

  • Hi Elveryx,

    have you considered using Azure SQL database instead of table storage, SQL server is designed for relational data,

    ----------------------------------

    Please mark as answered if it helped


    Please mark as answered if it helped Vishal Narayan Saxena http://twitter.com/vishalishere http://www.ogleogle.com/vishal/

    Monday, August 5, 2013 7:05 PM
  • Hi,

      >> Is there an efficient way to do this which doesn't kill the system if there are millions of users

    Table storage can be sorted by partition/row keys. If the scores are static, you can change your role key to be score + '_' + friend's ID. Then the entities will be sorted automatically by scores. But if the scores are dynamic (as in your case), then I have not had an optimal solution. However, a player usually will not have too many friends (even on Facebook a typical user may has less than 1000 contacts). So query the data in random order and then do an in memory sort would not take too much time.

    Best Regards,

    Ming Xu


    Ming Xu
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 6, 2013 1:05 AM
  • Thank Ming Xu,

    Assume that the player has 1000 friends.

    I would need to query the list of friends from the friend table. Then make 1000 query from the score tables before do an in- memory sorting since AzureTable doesn't support query by a list of value or in range? isn't that expensive? 

    Wednesday, August 7, 2013 5:04 AM
  • Hi,

      >> Then make 1000 query from the score tables before do an in- memory sorting

    It seems you don't need 1000 queries. A single query can return all friends data. Even if the scores are stored in a different table, you can use several queries with the "or" relationship (a single query may result in a very long URI, so several queries may be needed). If you use LINQ, you can also dynamically build an expression tree to mimic SQL's IN keyword as described in http://social.technet.microsoft.com/wiki/contents/articles/15698.where-in-and-not-in-queries-with-wcf-data-services.aspx. In addition, sorting 1000 items in memory is usually very fast (but maybe not 1000000 items).

    Best Regards,

    Ming Xu


    Ming Xu
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 7, 2013 2:09 PM