Yesterday we reviewed some tips on how to optimize query performance through the use of # tables and indexes. The resulting query performance increase was dramatic. In our real world scenario the time dropped from 30 seconds to under a second.
In our comments, AZ Jim brought up the fact that the two queries we execute could be materialized permanently through the use of Indexed views on the base tables. This is a great technique, and works well in reporting databases. In our situation, this is an OLTP database, where you want to avoid Indexed Views. And, to be fair to Jim, you should also avoid reporting from your OLTP database as well.
This brings me to two different points. It is a good practice for a database with more than a couple of users, that is very active in data entry, to have a reporting instance of the database, reducing contention on data entry.
Even then, with something like the scenario we have been reviewing, another option to have a materialized version of the reporting table structure is a data mart. The data mart works very much like an indexed view. The big difference is that you control when/how you populate the data mart. With the Indexed View, it is maintained whenever data is modified. Even in a reporting database, this can be a heavy load when many normalized records are modified. All of your normalized table updates will be blocked until the indexed views are also modified.
We went to a specialized query in order to get performance in the first place. Using the indexed view we made it so that the pivoting and filtering were done only when the data was modified, not every time the report was generated. However, we put a heavy load each time data is modified, remember, the query was slow.
Another option, one I’m sure Jim knows as well, is to take the records you are modifying and save the primary keys in a queue table. Then another background process can monitor that queue, and build the data in a data mart table, without having to process every row in a single batch, spreading out the conversion load over more time.
If your OLTP database is also serving as a reporting database, the datamart with a queue table is a compromise balancing the load of data entry and reporting.
What techniques do you use for reporting when you have a single database, and there is contention for data between data entry and reporting? Why not share your thoughts in a comment?
Cheers,
Ben