Home > Windows Azure, Windows Azure Storage > Improve Azure Table performance with query projections

Improve Azure Table performance with query projections

                At build 2011 Microsoft announced improvements in REST APIs used for Azure Storage with version (“2011-08-18”). Out of these improved features, a specific feature we are going to discuss here is about query projection while selecting entities from Azure Table storage.  In one of our project we had requirement for this feature but due to it’s unavailability we couldn’t implement it. Now, with this feature we will have to change our implementation but this change is good as it helps improve performance.

Earlier, selecting a specific or few properties from the Azure Table Storage was not supported. For example, in one of our implementations we were using a flag IsDeleted in table storage (with somewhere around 50 more properties) to maintain soft delete of an entity. Just to retrieve IDs of the entities we had to retrieve entire entity/ies with 50 properties and that would unnecessarily increase the latency and bandwidth load for the application.

e.g.

var query = from entity in playersServiceContext.CreateQuery<Players>(PlayersTableName)  where entity.IsDeleted == true;

 Now, with Version (“2011-08-18”) we can select specific properties using Select predicates which reduces the latency, bandwidth and improves the performance of application.

var query = from entity in playersServiceContext.CreateQuery<Players>(PlayersTableName) where entity.IsDeleted == true
select new 
{
  PlayerName =  entity.PlayerName,
  Rank = entity.Rank
};

Here, select new does the trick using anonymous entity.

We could explicitly project the properties into a specific entity type like 

var query = from entity in playersServiceContext.CreateQuery<Players>(PlayersTableName) where entity.IsDeleted == true
select new DeletedPlayers
           {
             PlayerName =  entity.PlayerName,
            Rank = entity.Rank
           };

Here, DeletedPlayers is a DataServiceEntity can be called as partial view entity.

In one of the other scenarios, we wanted to display the name, rank and age of players in a HTML table and then user might go into the details (view/edit) of the player using a link from the table. We had used paging size of 10 to decrease latency as there were around 50 properties attached with every player entity. Earlier, we had to retrieve all the 50 properties but now with this select predicate we can get only specific properties (Name, Rank, Age and Core properties) for the entity. This gives us better performance and an option to think about displaying more than 10 entities on a page with a good performance.

A few things to remember when using select predicate with Azure Table Storage.

  • If you are not defining core properties [PartitionKey, RowKey and ETag] in your data service entity definition then it would by default get selected in select predicate
  • In case you are explicitly defining core properties then you will have to explicitly select it in select predicate which would be useful for updating the properties
  • Prefer explicit entities (Partial) over anonymous entity as it makes the looping and updates explicit and improves readability
  • Remember even if you want to select only one property in the select predicate still you will have to at least use the anonymous entity. Just select entity.entityproperty would not work

So, go ahead and revisit and change your code for betterment where you had taken decisions just because query projections were not supported for Azure tables.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s