Wednesday, June 9, 2010

ADS 10 Tip #8 – Table Data Caching

The Advantage cache system introduced in version 8.1 and has continued to improve. In version 10 all temporary tables are created in the cache instead of on disk. This improves performance by reducing the amount of time it takes to create the temporary tables as well as improving read and write speed. Temporary tables may still be written to disk when there is not enough space available within the cache.
Version 10 also added the ability to specify tables to be loaded into the cache system. This is primarily intended for relatively small tables that are accessed frequently. By loading these physical tables into memory reads and writes to the table can be much faster. However, changes made to tables stored in cache may not be persisted to disk if the server is improperly shutdown. If the server was improperly shutdown you may receive a 7155 "Lost Cached Updates" error the first time you open the table after the improper shutdown.  For more information refer to Table Data Caching in the help file.
TableDataCachingTables can be set to cache either reads or writes but not both. You can set a table for caching of reads only or for both reads and writes. You can specify the caching option when creating a table or when opening a table. Two new options ADS_CACHE_READS and ADS_CACHE_WRITES   have been added which can be specified when using AdsCreateTable90 or AdsOpenTable90. Additionally the TAdsTable.AdsTableOptions includes an AdsCachingOption allowing you to specify read or write caching.
Dictionary bound tables include a Table Caching property which you can modify in the Table Designer on the Table Properties tab in Advantage Data Architect. This property can also be modified using the sp_ModifyTableProperty or AdsDDSetTableProperty API. You can get the current setting using AdsDDGetTableProperty. An example of using the sp_ModifyTableProperty system procedure is below.

// Turn off table caching
EXECUTE PROCEDURE sp_ModifyTableProperty
  ( 'tblname', 'TABLE_CACHING', '0' , 'NO_VALIDATE', NULL );

// Set the Table to always cache reads
EXECUTE PROCEDURE sp_ModifyTableProperty
  ( 'tblname', 'TABLE_CACHING', '1' , 'NO_VALIDATE', NULL );

// Set the Table to always cache writes
EXECUTE PROCEDURE sp_ModifyTableProperty
  ( 'tblname', 'TABLE_CACHING', '2' , 'NO_VALIDATE', NULL );


Peter Funk said...

Just to clarify, when you enable caching of writes, caching of reads is also enabled. Read caching is assumed when you enable write caching.

Lindsey said...

Heello mate great blog post