title: Mirroring or caching database views in intermediary layer for web application?
body: <p>I have an Oracle 11g database with an <strong>extremely</strong> complex and badly designed schema. This is a legacy system with many dependencies that supports several critical applications, modifying the schema of this database is unfortunately out of the question.</p>
  
  <p>I am developing a web application (ASP.NET MVC 5) that acts as a <strong>read-only</strong> status dashboard for the information in this database. Currently, I rely on purpose built database views to get only the information the web application needs. Given the complexity of the schema many of these views perform very poorly. When the web application is busy with many users, the database struggles to keep up, usually resulting in time out errors. Also, when the database does fail for whatever reason, the web application cannot show any data. Users would still like the web application to show a snapshot of the data before the database failed.</p>
  
  <p>The nature of the data is very dynamic, rows are being added/updated/deleted by several external systems and processes constantly, and I have no way of knowing when there is a change to the underlying data, so I have to re-query the view to get fresh data.</p>
  
  <p>Because of this situation, we are considering removed the direct link between this database and the web application and instead create some sort of intermediary cache/database/magic layer between them. This way, the web application would get its data from this intermediary later without placing heavy load on the complex database. When the complex database fails, the web application can still query the last snapshot of data from this intermediary layer.</p>
  
  <p>The question is, what should this intermediary layer be? Because I don't know when and how the underlying data changes I can't maintain a live cache of the data. Instead I would need to rely on snapshots of the views in this database.</p>
  
  <p>This is our current idea:</p>
  
  <p>We create a new, intermediary SQL Server/Oracle database. A job runs every 2 minutes for each database view we are currently using, queries it, then dumps the results into a table in our intermediary SQL Server/Oracle database. This would require truncating the intermediary table, then refilling it with the fresh view result data. In the meantime, the web application would be querying these intermediary tables for data. The obvious concern is what happens when the web application is trying to query the intermediary table while it is being truncated and repopulated with fresh data? Another concern is dealing with possible concurrency issues when grabbing data from views that share foreign keys or related data.</p>
  
  <p>Normally, a web application would simply maintain a cache, but this would require being hooked into the add/update/delete events in the database to maintain the state of the cache. On top of that, the cache wouldn't be a full snapshot of the database. If the database were to fail, the cache would be unable to provide a snapshot of data from before the failure.</p>
  
  <p>Any other suggestions on what this magical intermediary layer should be? We are looking at solutions available on both the database end (either SQL Server or Oracle) as well as solutions on the web application side (ASP.NET MVC 5, IIS).</p>