Caching queries using SQL views
A customer approached us as they were having problems with report performance. The problem related to a report being based upon a view that took 90 seconds to run. I didn’t think that was too slow until they told me that there were hundreds of concurrent reports all hitting the same query.
First response was to rewrite the view to see if it could be made more efficient. However, the view was very complex and trusted, so needed to be left as was.
My second response was to drop the data into a small datamart, then change the report’s connections. That wasn’t suitable as they needed the data to be up to the minute.
No problem, let’s use an Indexed View (materialised view for Oracle users). That failed too as Microsoft SQL Server doesn’t support views with outer joins or sub-queries. I’ve no idea why, as Oracle manages it!
After much thinking, I came up with the idea of caching a view. Originally, I thought about changing the report to use a Stored Procedure so that the data could be cached. However, due to the high concurrency, I wasn’t happy about the caching algorithm. As there were many reports, it was also better if the original view definition was used to save time changing all the reports.
The final solution was a scheduled stored procedure. This used the original view’s definition to populate a database table, adding indexes to improve performance. Each time the stored procedure runs, a different cache table is generated. Once populated, the view used by the reports is changed to point to the newly cached data. Five cache tables are used to ensure that reports that are running never lose their data source.
End result – 90 second test query now takes less than 2 seconds to run.
At the moment, creating the necessary tables and views is mostly a manual process. However, if there’s interest in this as a product, I may create a user interface to allow better management.
