title: Do I use Azure Table Storage or SQL Azure for our CQRS Read System?
body: <p>We are about to implement the Read portion of our CQRS system in-house with the goal being to vastly improve our read performance.  Currently our reads are conducted through a web service which runs a Linq-to-SQL query against normalised data, involving some degree of deserialization from an SQL Azure database.</p>
  
  <p>The simplified structure of our data is:</p>
  
  <ul>
  <li>User</li>
  <li>Conversation (Grouping of Messages to the same recipients)</li>
  <li>Message</li>
  <li>Recipients (Set of Users)</li>
  </ul>
  
  <p>I want to move this into a denormalized state, so that when a user requests to see a feed of messages it reads from EITHER:</p>
  
  <p><strong>A denormalized representation held in Azure Table Storage</strong></p>
  
  <ul>
  <li>UserID as the PartitionKey</li>
  <li>ConversationID as the RowKey</li>
  <li>Any volatile data prone to change stored as entities</li>
  <li>The messages serialized as JSON in an entity</li>
  <li>The recipients of said messages serialized as JSON in an entity</li>
  <li>The main problem with this the limited size of a row in Table Storage (960KB)</li>
  <li>Also any queries on the "volatile data" columns will be slow as they aren't part of the key</li>
  </ul>
  
  <p><strong>A normalized representation held in Azure Table Storage</strong></p>
  
  <ul>
  <li>Different table for Conversation details, Messages and Recipients</li>
  <li>Partition keys for message and recipients stored on the Conversation table.</li>
  <li>Bar that; this follows the same structure as above</li>
  <li>Gets around the maximum row size issue</li>
  <li>But will the normalized state reduce the performance gains of a denormalized table?</li>
  </ul>
  
  <p>OR</p>
  
  <p><strong>A denormalized representation held in SQL Azure</strong></p>
  
  <ul>
  <li>UserID &amp; ConversationID held as a composite primary key</li>
  <li>Any volatile data prone to change stored in separate columns</li>
  <li>The messages serialized as JSON in a column</li>
  <li>The recipients of said messages serialized as JSON in an column</li>
  <li>Greatest flexibility for indexing and the structure of the denormalized data</li>
  <li>Much slower performance than Table Storage queries</li>
  </ul>
  
  <p>What I'm asking is whether anyone has any experience implementing a denormalized structure in Table Storage or SQL Azure, which would you choose?  Or is there a better approach I've missed?</p>
  
  <p>My gut says the normalized (At least to some extent) data in Table Storage would be the way to go; however I am worried it will reduce the performance gains to conduct 3 queries in order to grab all the data for a user.</p>