One database optimization solution is to duplicate a database, or a portion of it, when the database supports both online processing and reporting.
The basics of the practice is to have two databases, essentially the same. All data entry is made into the OLTP instance of the database. All of the create, update and delete actions are replicated into a second database that is used for selects.
The data may be replicated using a number of techniques. Database Mirroring is probably the easiest. Replication is a solution that works when the entire database does not need to be mirrored, only a subset of the tables being modified. Those are two solutions built into the bigger database engines.
This isn’t an SQL only technique. You can roll your own replication techniques for just about any data store. The point is to use a CQRS implementation where the data modification calls to your data store are separate from the data retrieval calls .
This separation increases performance because your transaction instance of the database has a minimal set of indexes to optimize data manipulation. The reporting instance may have many more indexes. However, it isn’t necessarily responsible for enforcing referential integrity and things of that nature. Instead it has more indexes, or more complex indexes, for the purposes of data retrieval.
The reporting database may also have data marts (de-normalized tables) and/or indexed views. These additional database objects take longer to maintain than a finely normalized database. However, since your data modification transactions aren’t waiting for them to be updated, they can take a little bit longer to update, while maintaing optimum performance for your online processes.
Yes, this is a high level overview. There is a lot of fine tuning to be added. My intention is to expose you to this implementation strategy should it be useful in your projects. Have you used this implementation strategy? Share your experience here, or by email to btaylor@sswug.org.
Cheers,
Ben