Benefits of Precomputed Result Sets

Whether your site benefits from precomputed result sets depends on how they are designed.

Although you may want to precompute as many queries as possible (particularly more joins) and make them available for multiple queries, precomputed result sets take extra disk space and have a higher maintenance cost. You can create extra indexes to help query performance, but these also incur an extra maintenance cost.

Precomputed result sets are best for frequently executed, expensive queries, such as those involving intensive aggregation and join operations. When you submit a query, the optimizer attempts to rewrite the query to use existing precomputed result sets instead of the base tables.

Generally, capture your application’s workload and design your precomputed result sets based on this workload. A good place to start is to create a combined join graph for all queries—along with their frequency of use—to indicate good candidates for using the same precomputed result set for multiple queries.

Test your precomputed result sets before putting them into production. If the queries are read-only or read-most, measure their performance gain against the extra disk space they use and the amount of time it takes them to populate the data; if it is a mixture of read-only or read-most, measure the impact of the precomputed result sets against the throughput.