If you frequently need to read the same data from the database in the same transaction (for example look-up table data), the DBMS cache/buffer should reduce the I/O to disk by having the data in memory. However there is still a considerable overhead involved with using SQL to obtain the data multiple times.
A technique that we have used to improve performance is an application cache. This uses a common action block to read the data. The action block checks to see if the data is cached, and if it is, avoids the need for a READ from the database. If it is not available, it reads the data and then stores it in the cache.The cache is implemented using uninitialised local views and you can store as many rows as you are prepared to allocate memory to.
The design needs to ensure that the cache is initialised at the start of the transaction (for C generated code when using the TE) and also to cater for the possibility of updates to the database, i.e. the cache needs refreshing or deleting if the data can be changed during the transaction.
In specific examples where we have used the cache for heavily accessed tables, we have found the cache improves the performance of the READ action block by 1000%.
8 comments:
hi,
Great example of CG application performance improvement. But would it be possible to show an example?
In my case we use CABs to read data from reference tables (created by us)
Ex:
Car - Door -possible colors:
1 - white
2 - black
3 - blue
Possible colors are on a ref table that has to be read all the time if we want to show description of car door colors on a list
A simple example would be:
CASE OF COMMAND
CASE clear
--NOTE clears the cache
--SET LAST OF group-temp-cache TO 0
CASE update
--NOTE update cache with new value if record updated
--FOR SUBSCRIPT OF group-temp-cache FROM 1 TO LAST BY 1
---IF temp-g ent id = in-updated ent id
----MOVE in_updated ent TO temp-g ent
<----ESCAPE
CASE OTHERWISE
--NOTE read from cache
--FOR SUBSCRIPT OF group-temp-cache FROM 1 TO LAST BY 1
---IF temp-g ent id = in ent id
----MOVE temp-g ent TO out ent
<---ESCAPE
--NOTE not found in cache
--READ ent WHERE id = in ent id
---MOVE ent TO out ent
--IF group-temp-cache IS NOT FULL
---NOTE add to cache
---SET SUBSCRIPT OF group-temp-cache to LAST + 1
-- MOVE out ent to temp-g ent
Thanks for your example.
When should the clear command be used?
On transaction finish?
No, on initialisation as described in the main post.
Thank you very much for your tips. I've managed to do a test on one of our applications.
Some transactions access the same AB about 20+ times where I've managed to reduce the amount of BD access by 1/3.
I'm considering expending this to other transactions where the same static data keeps constantly been read from the database.
Glad you found this useful. The only caveat I would add is that this approach is only really worth doing when you have a serious performance issue that you want to address, and I am not sure I would want to make this a 'standard' approach since it will increase the memory usage of the load module.
Thanks. Is the max size of the group view allocated in memory upon initialize or is it dynamically expanded as the group view increases?
The ideal solution would be calculating the max # of rows that are sufficient to hold in memory...
It is always allocated in full, but when depends on the OS. See previous posts on the issue of memory allocation.
Post a Comment