Performance of Azure Table Storage Degrades with More # of Columns in Azure Table
When we are having Azure table with 150 + Columns performance of really degrades. For Example : If i have Customer table with 150 Columns and I need to transform Customer Table with specific 25 columns to CustomerDTO object,
Here is the following code used to convert Table object to DTO Object
public IEnumerable<T1> ExecuteQuery<T, T1>( string predicate )
where T : TableEntity, IDomainData, new() //T is Customer Table Entity
where T1 : new() //T1 is CustomerDTO
Type typeParameterType = typeof(T);
CloudTable tableReference = tableClient.GetTableReference(typeParameterType.Name);
var query = new TableQuery<T>();
query.SelectColumns = typeof(T1).GetListOfPropertyNames();//Gets the Property list of target Object.
query.FilterString = predicate;
return tableReference.ExecuteQuery<T, T1>(query, EntityAdapter.AdapterResolver<T1>);
In the EntityAdapter, we use TableEntity.ReadUserObject(this.InnerObject, properties, operationContext); to convert to Destination Object type,
this approach takes more time to get less number of columns from server ( i.e Azure Table with more than 150 columns) than below approach of Client Deserialization with all table columns returned from server
Where(row => row.PartitionKey == period && row.RowKey.CompareTo(startRowKey) >= 0 &&
row.RowKey.CompareTo(endRowKey) < 0).AsTableQuery<CustomerDTO>()
Here CustomerDTO tries to query Customer Azure table, pulls all the columns to client and deserialise the Target object type i.e CustomerDTO object which has subset of Azure Table Customer.
So our analaysis tell us that server side projection is taking more time than client side projection in azure table. Esp. when we are having Azure table with huge number of columns
Thanks for the request! Also, please refer to our new Azure Storage Table Design Guide to learn how to design scalable and performant tables.