Re: memory resident tables

andy lennard wrote: > > In article <74c6st$ukq$1@nnrp1.dejanews.com> awallner@eb.com writes: > > > I am trying to use the not well documented command: > > set table <tablename> memory_resident; > > to force tables to stay in main memory. > > The command executes ok, but I don't see the expected performance > > improvement. > > Has anyone successfully used this and obtained significantly better > > response times ? > > Alfred Wallner > > I'm not sure how I'd quantify performance improvement for this.. > If a table sees 'heavy' use then it will effectively be 'memory resident' > because the info will all be present in the LRUs, dic etc. Forcing a table > to be memory resident will not help in this case. > > For a lightly used table, forcing it to be resident may help in those > 'rare' occasions when the table is accessed, and give snappier > results for the 'once a day' user. (but presumably to the detriment of > other users whose tables are 'coming and going' normally). > > Real improvement would also depend on the number of rows to be returned > from this table. Sure, the first row may come back faster, but if there > are another 20,000 following it then that may be the major factor. > > Perhaps the poor documentation reflects the poor gains to be achieved.... Where memory resident tables can REALLY help is on a VERY busy system where several tables may be busy but a few are accessed only periodically but performance is critical. In this case without Residence such tables will be swapped out in favor of more recently accessed data. With Residence set on the table only activity on another resident table can cause a resident table's pages to be reused. This can speed up programs that need instant access to certain data but are not run constantly such that they force LRU algorithms to keep the active pages resident. Art S. Kagel