Output Caching is a technique used by software developers to improve application performance. If a result set takes a long time to produce, then it is mandatory to have it cached in some temporary storage and reuse the cached data for a period of time.
This consideration applies when using stored procedures or web services outputting a custom result set.
One method is to have the output stored in the server memory. This works great when the data has already been processed. For example, it makes a lot of sense to capture a fragment of a web page in the server memory cache. An application extracts the fragment from cache and sends it to the web browser when needed.
In a data centric application the final shape of data presented to the users is not known. Users may need to sort and filter data. This will require caching a custom result set in a native format. The server memory cache is extremely fast but will not tolerate large datasets. Besides, developers will not be able to execute standard SELECT statements against an in-memory result set when responding to filtering and sorting requests from the user. The natural solution is an output cache table.
An output cache table is a database tables designed to temporarily store a custom result set produced by a stored procedure, web service, or any other resource-intensive data source.
An output cache table must have columns that match every column in a custom result set.
Additional columns representing parameters of a stored procedure or web service producing a custom result set will improve the reusability of the data stored in the output cache table.
If the cached data cannot be shared with other users, then an additional column representing the user identity must be added to the structure of the output cache table.
The temporary nature of cached data requires a column capturing its expiration date and time. An application using the output cache table must be able to detect the “stale” data and repopulate the cache with fresh content.
Code On Time web applications work really well with database tables in general and will effortlessly handle an output cache table. SQL Business Rules will help populating an output cache table at just the right time.
Custom search bars and search dialogs allow easy input of parameters that must be passed to the stored procedure or web service. The input parameters may also be passed through master-detail relationship filters.
The built-in filtering performs a seamless segregation of output cache table content based on the end-user identity.