Benefits Of Caching
Just like database and disk caching, application caching can help avoid unnecessary IO and network traffic. Accessing data from local memory or disk will vastly outperform accessing the same data over a network. This is especially important if you are using client server connections across a WAN, but also provides benefits for appserver and shared memory connections.
Depending on your application and OpenEdge configuration different methods of caching will make more sense than others. I will cover the most common caching approaches below and describe the types of problems they are designed to solve.
I will be using tables and sample code based on the OpenEdge "sports" database so we can have a common point of reference for discussing which approaches make sense for certain types of data.
Caching can be as simple as storing a copy of a relatively static lookup table (states, countries, etc.) or as complex as storing the results of complicated business logic. When deciding if a particular application function or subset of table data is a candidate for caching you need consider the factors below.
It is likely that the data in your application is modified at varying rates. Some data will be highly volatile and other data will be relatively static in nature. You should focus on data that can afford to be a few minutes (or hours) old. Very rare is the application that must have current versions of all of the data.
Volume of data
While it is true that large volumes of data can be cached and still perform better than networked access you should focus on smaller sets of data first. Large and small will have to be defined relative to your application but usually tables with fewer than 5000 records would be considered small.
Frequency of use
Obviously you are looking for sets of data that are frequently used, or at least frequently enough compared to the cache life cycle. If you have a set of data that is only used within a certain application function but is used repeatedly inside of the function caching will still improve performance.
There are two basic kinds of caching methods that you can use; session and procedure level caching. Session level does not imply that the data is cached for the length of the session, just that it is possible to share the cached data between multiple procedures. With procedure level caching the data is only stored for the life of that procedure. Both methods are usually implemented using Temp-Tables but this not 100% required.
Session level caching
Session level caching is best implemented by persistent procedures or static classes. With static classes you have the benefit of compile time syntax checking and no need to instantiate an object. Static classes do have the downside of requiring the OpenEdge client to restart in order to recognize new versions of the class, but without careful planning the same is true for persistent procedures.
I will be using static classes in the examples below for brevity but both versions are available for download. Note that although these examples work they are not intended to go directly into production and only to serve as proof of concepts.
Caching small tables
Small lookup tables can usually be cached with little regard to choosing specific columns. This example uses dynamic Temp-Tables to store every record for a table and supports a default time to live (TTL) or specific TTL per table.
Time to live specifies how long the cached version should be used. Once that time has expired the next call will refresh the cached copy from the database. Some tables may need to be refreshed every few minutes while others (like the state table) can safely be cached for much longer periods of time.
TableCache example code
The TableCache class is intended as a basic example of implementing table level caching in your application. Feel free to use the example code to expand on these concepts to integrate the appropriate style of caching into your application.
|Description||Static Class Link||Procedural Link|
|SetTTL(seconds)||Sets the default TTL for all tables. This should only be called from the startup procedure for your application.|
|SetTTL(table,seconds)||Overrides the TTL for a specific table. This should only be called from the startup procedure for your application.|
|EmptyCache()||Removes all cached copies of database tables.|
|ShowCache()||Displays cache statistics for every table that has been cached, including total database reads and cached reads.|
|GetTable(table,Temp-Table handle)||Copies the database table into the specified Temp-Table from the cached copy. If the cached copy is expired then it will also read from the database to refresh the cache. The Temp-Table does not have to contain the complete list of table columns but the column names and data types must match for them to be copied from the cached copy.|
Download the class into a directory that is included in your PROPATH and you will be ready to start caching data. Below is a very simple example of using the class to cache a table with the DO loop simulating multiple calls from different procedures.
DEFINE VARIABLE i AS INTEGER NO-UNDO. DEFINE TEMP-TABLE ttState NO-UNDO LIKE state. /*--- Load the Temp-Table 100 times, 1 from the DB and the rest from cache ---*/ DO i = 1 TO 100: TableCache:GetTable("state", TEMP-TABLE ttState:HANDLE). END. TableCache:ShowCache(). FOR EACH ttState: DISPLAY ttState. END.
Procedure level caching
Procedure level caching makes more sense when you are running a report or data extract and need to read smaller tables from inside a loop for a large transactional table. Common usages could be item names, states, user names, etc. It is even possible in some cases to cache complex business rules that will be repeated within the loop.
If you know that it is likely you will read most of a smaller table it makes more sense to cache the entire table outside of the loop. If you are going to read a subset of the entire table it makes more sense to cache the data on an as needed basis. This example using the sports database shows the concepts assuming real world data volumes of tens of thousands of customers and orders.
DEFINE TEMP-TABLE ttState NO-UNDO LIKE state. DEFINE TEMP-TABLE ttItem NO-UNDO FIELD item-num LIKE ITEM.item-num FIELD item-name LIKE ITEM.item-name INDEX IdxMain IS UNIQUE item-num. FOR EACH state NO-LOCK: CREATE ttState. BUFFER-COPY state TO ttState. END. FOR EACH customer NO-LOCK: FIND ttState OF customer NO-ERROR. DISPLAY customer.cust-num customer.NAME ttState.state WHEN AVAILABLE ttState ttState.state-name WHEN AVAILABLE ttState WITH FRAME a. FOR EACH order OF customer NO-LOCK, EACH order-line OF order NO-LOCK: FIND ttItem OF order-line NO-ERROR. IF NOT AVAILABLE ttItem THEN DO: FIND ITEM OF order-line NO-LOCK NO-ERROR. CREATE ttItem. ASSIGN ttItem.item-num = order-line.item-num ttItem.item-name = (IF AVAILABLE ITEM THEN ITEM.item-name ELSE "Invalid Item"). END. DISPLAY order.order-num order-line.Line-num order-line.item-num ttItem.item-name WITH NO-BOX FRAME b DOWN. DOWN WITH FRAME b. END. END.