Difference between revisions of "SET TCACHE"

Revision as of 10:51, 3 June 2009

Class

SQL Applications

Purpose

Enable, disable and configure table caching during SQL operations

Syntax

SET TCACHE ON | OFF | TO <expN>

See Also

ALTER INDEX, ALTER TABLE, CLOSE, CLOSE DATABASES, CREATE INDEX, CREATE TRIGGER, DB_MAXWKA, DELETE, DROP INDEX, GRANT, INSERT, REVOKE, SELECT, UPDATE, USE

Description

The SET TCACHE command is used to enable or disable table caching and configure the number of tables that can be cached during SQL operations. If SET TCACHE is ON, a table accessed by an SQL statement is left open until the session or connection is closed. With SET TCACHE OFF, tables are opened and closed with every SQL statement.

With SET TCACHE ON, the default number of tables that can be cached corresponds to the number of available workareas (DB_MAXWKA environment variable / symbol). This number can be reduced using the SET TCACHE TO <expN> command. The <expN> specifies the maximum number of tables that can be cached.

Enabling TCACHE can give significant performance benefits where multiple operations are being carried out on the same table or tables. With TCACHE ON, individual tables can still be closed if required using the USE command in the relevant workarea or the CLOSE <alias> command.

Example

// Up to 4 tables will remain open after a completed SQL statement
set tcache on
set tcache to 4
set sql to recital
 
EXEC SQL
  OPEN DATABASE southwind;
 
EXEC SQL
  INSERT INTO shippers
  VALUES (4,"Recital Corporation","(978) 921-5594");
 
EXEC SQL
  UPDATE employees
  SET extension="256"
  WHERE employeeid=4;
 
EXEC SQL
  DELETE FROM suppliers
  WHERE supplierid=1;
 
 
EXEC SQL
  ALTER TABLE example
  ADD (email char(40));
 
EXEC SQL
SELECT * from products;
 
// Next free workarea is workarea 5
// as tables in workareas 1 to 4 remain open
? workarea()
?
close databases

Products

Recital Database Server, Recital Mirage Server, Recital Terminal Developer