Application caching is where data can be stored in the memory of a client computer. That client computer can even be on the other side of the world, using an Internet browser. Application caching is significant to database model performance because the best things to cache are static tables.
In any database, it can sometimes be an effective performance-tuning method to cache static data, or even index structures, even dynamic table indexes. This caching process can be implemented on the database server, within the server, on an application server, and even in the application itself, even as far as a front-end application passed to a multitude of unknown users over the Internet (in a browser).
For OLTP databases, just the static data tables can be cached, even perhaps some dynamic table indexes as well. Caching makes perfect sense for OLTP databases because very high levels of sharing are prevalent (high concurrency). For example, an online bookstore might perform much better for a user buying books if all recently accessed static files, by that specific user, were cached as soon as the user logged in. Sometimes, even partially static files can be preloaded into an application such as an online bookstore.
Common types of structures in this category for online bookstores as likely candidates for online browser caching are wish lists, the contents of a shopping cart, previously purchased items, and the list goes on. Caching of any form is completely useless in data warehouses because fact tables are usually so incredibly huge in comparison with dimensions. Also, tables are accessed infrequently and by very few users at once. For data warehouses, quantities of data in each table have much more significant impact. Caching large data warehouse tables in memory would fill hardware resource capacities rapidly. Data warehouses are simply I/O intensive and that is all there is to it.
The previous tip was excerpted from Chapter 8, 'Building Fast-Performing Data Models,' from the book "Beginning Database Design" by Gavin Powell, courtesy of WROX Publishing. Click here for the complete collection of book excerpts.