Difference between revisions of "SET DCACHE"
Yvonne.milne (Talk | contribs) |
Yvonne.milne (Talk | contribs) |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 8: | Line 8: | ||
==See Also== | ==See Also== | ||
| − | [[SET CACHELOAD]], [[SET ICACHE]] | + | [[SET CACHELOAD]], [[SET ICACHE]], [[SET SQLCACHE]] |
==Description== | ==Description== | ||
| Line 19: | Line 19: | ||
Using this formula with a table cache of 500, record 1 maps into slot 1, record 501 also maps into slot 1. When a slot is referenced which already contains a record, and that record has been modified since it was read from the table, then the table is updated with the modified record and the required record is read into the cache slot. When a table is closed, modified records held in memory are written into the table. | Using this formula with a table cache of 500, record 1 maps into slot 1, record 501 also maps into slot 1. When a slot is referenced which already contains a record, and that record has been modified since it was read from the table, then the table is updated with the modified record and the required record is read into the cache slot. When a table is closed, modified records held in memory are written into the table. | ||
| − | The SET DCACHE command has no effect if a table is shareable | + | The SET DCACHE command has no effect if a table is shareable. The SET CACHELOAD command can be used in association with the SET DCACHE command. This forcibly loads the table and index cache when the table is used exclusively. The SET ICACHE command can be used to ’cache’ index keys in memory. By default, DCACHE is ON for the SQL Engine (for temporary resultsets, which are not shared) and OFF otherwise. |
==Example== | ==Example== | ||
| Line 39: | Line 39: | ||
[[Category:Set_Commands|DCACHE]] | [[Category:Set_Commands|DCACHE]] | ||
[[Category:Performance and Optimization]] | [[Category:Performance and Optimization]] | ||
| − | |||
| − | |||
Latest revision as of 11:12, 20 March 2013
Contents
Purpose
Map table into memory
Syntax
SET DCACHE TO [<expN>] [OPTIMUM]
SET DCACHE ON | OFF | (<expL>)
See Also
SET CACHELOAD, SET ICACHE, SET SQLCACHE
Description
If SET DCACHE is ON when a table is used, then the table records will be ’cached’ in memory, and the records which are not held in memory will be read in when they are needed.
The SET DCACHE TO <expN> command allows the number of records held in memory to be specified on a per table basis. If the optional OPTIMUM keyword is specified, the optimum dcache size will automatically be set. The table ’cache ’ area acts as a ’window’ into the table. Each record referenced in the table is ’mapped’ into the table cache using the simple formula:
position = mod(<record number>, <cache size>)
Using this formula with a table cache of 500, record 1 maps into slot 1, record 501 also maps into slot 1. When a slot is referenced which already contains a record, and that record has been modified since it was read from the table, then the table is updated with the modified record and the required record is read into the cache slot. When a table is closed, modified records held in memory are written into the table.
The SET DCACHE command has no effect if a table is shareable. The SET CACHELOAD command can be used in association with the SET DCACHE command. This forcibly loads the table and index cache when the table is used exclusively. The SET ICACHE command can be used to ’cache’ index keys in memory. By default, DCACHE is ON for the SQL Engine (for temporary resultsets, which are not shared) and OFF otherwise.
Example
// Open up payroll system set exclusive on set cacheload on set icache to 3000 set dcache to 1000 use payroll index pay_date, emp_code // Cache the employee records in memory set dcache on use employees index name, emp_code set dcache off use wages index emp_code
