- Can caching be used? File server and Web servers are optimized for sending large files, whereas RDBMs are optimized for set based operations and do not always scale well for large operations. Caching will work best if the dynamic queries repeat themselves or if portions of them are updated. For example, most search engines take advantages of the fact that 90% of the searches are on the same topics, and these search results are cached and sent to the client without hitting the full-text catalogs at all. These cached results are updated continuously. Naturally this will only work if you know in advance what the dynamic data sets will look like.
- Can the rest be batched and send to the web client asynchronously? Programming for Web clients require special programming techniques in dealing with large dynamic datasets to prevent the request from timing out. On the database you want the queries to run as fast as possible. Time spent in denormalization and creating covering indexes will provide the greatest impact on performance.
- Can you use paging? Paging will only display the first 10, 25, or 100 results on the client. If the Web application does not need to display or consume the entire dynamic data set at one time you can return the first 100 results to the Web client and then generate the entire dynamic data set in the background so that repeat request will be returned from this pre-generated dynamic data set.
- Can you use query notifications? Query notifications will keep a dynamic dataset updated with newly inserted rows. It is a feature of SQL Server 2005 and ADO.Net 2.0. If a change occurs in the data underlying the dynamic dataset a notification will be made to refresh the dataset.
- Is the RDBMs the right tool for the task? I attended a presentation where a company displayed their reporting tool which displayed all sort of aggregated content. The backend was a SQL Server database. As the reporting tool dealt exclusively with aggregations and drill downs the natural choice should have been Analysis Services.
This was first published in February 2007