title: Persistent DataCache with Pub/Sub OnChange
body: <p><em>(The following question is a bit long so if you're not interested in persistent cashing and notifications feel free to press the x-button in your browser!)</em></p>
  
  <p>We've been tasked to implement a persistent data store that will keep a lot of numerical information.</p>
  
  <p>Basically the use case is:</p>
  
  <ol>
  <li><p>You'll get feeds with data updates and insertions which need to be propagated in the data store. </p>
  
  <p>1.1. <strong>Frequency of updates</strong> currently ranges from 1 feed from 1 to 10 secs. But later on we may get more so scalability maters.</p>
  
  <p>1.2. <strong>Volumes</strong>. Each feed is going to be roughly 100K of rows (even if a value does not change it'll still be in the feed)</p></li>
  <li>The values need to be persisted. Once this happens then the C# server needs to be notified of the even. Ideally with an event. Because the feeds will contained data that have not changes ideally the server event should come along with the delta and not just <code>OnStuffChanged</code>.</li>
  </ol>
  
  <p><strong>Implementation</strong></p>
  
  <p>Because the system is using SQL server some non-technical people have signed off usage of sql server for the implementation. This kind of smelled bad to me! But in any case did some research and found about a Wrapper on the <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx" rel="nofollow">SqlDependency</a> API.</p>
  
  <p>I saw however that the appi comes with a luggage, in the form of a <em>long list of constraints</em>(<code>http://msdn.microsoft.com/en-us/library/ms181122%28v=sql.105%29.aspx</code>). I also saw that MS say that it's not that efficient for sub second performance (which is not currently the case, but could be in the future).</p>
  
  <p>Specifically for performance &amp; reliability MS say </p>
  
  <blockquote>
    <p>"Query notifications might not be the best choice for applications when notifications must be received with a sub-second response time, when the network infrastructure is not both fast and reliable, or when the volume of notifications is very high."</p>
  </blockquote>
  
  <p>and suggest <a href="http://msdn.microsoft.com/en-us/library/ms187528%28SQL.105%29.aspx" rel="nofollow">alternatives to query notification</a> such as:</p>
  
  <ol>
  <li><strong>After Update trigger</strong>(I'm not a big fan of triggers tho) on the monitored table whose action is to use the SQL Server service broker to send a message to the update needing notification.</li>
  <li>Implementation of <strong>custom middleware</strong> that stores and notifies.</li>
  </ol>
  
  <p><em>(guys I'm getting to the point thanks for being patient!)</em></p>
  
  <p>So the point is that I really feel that none of these ideas other than the custom middleware is good. </p>
  
  <p><strong>Questions</strong></p>
  
  <ul>
  <li><em>Has anyone had hands on experience with <code>SqlDependency</code> and <code>Service Broker</code></em>? Do you reckon I should be starting a crusade against mgmt in order to prevent a tech mistake from happening in the 1st place?</li>
  <li>I kind of think that perhaps I should be using something like <code>redis/memcached/mongo</code> for the data cache. They offer persistency. I'm sure I can figure out a way to bridge them with a relational DB and provide the changed/new numbers to the server for processing. Doesn't this make more sense?</li>
  <li>Maybe I'm just over-engineering the thing. Should I instead be trying <strong>other suggestions</strong>(a similar stackoverflow question suggested ``)?</li>
  </ul>
  
  <p>Apologies for the lengthy post! Thank you in advance if you've bothered reading up to this point!</p>