Controls whether or not a query, with a simple sql select statement (of the form "select * from <table>), is sent directly to the DBMS for execution or is setup to behave like a local database table.

Property of

Query

Description

When the usePassThrough property is set to False (the default):

For query's using a simple sql select statement (in the form "select * from table") which meet the conditions listed below in "Conditions for Dynamic Caching", the query's rowset, when activated, is setup to behave like a local, file based database table such as a dBASE .dbf table.

The query result set is managed using a dynamic caching algorithm, as described below in Dynamic Caching Behavior", which supports the use of index and key-oriented operations.

Query's using a complex sql select statement, or those which do not meet the conditions described below in Conditions for Dynamic Caching, will be executed as if the usePassThrough property were set to True.

When the usePassThrough property is set to True, the query's sql statement, is passed directly through to the database server for execution and the resulting rowset uses a more basic caching algorithm, described below in "Basic Caching Behavior". The query result set cannot use most index, or key oriented operations.

Conditions for Dynamic Caching

Query's sql property must contain a simple select statement ("select * from table").

The database server must support row ID's and/or the table must have a unique or primary key index defined.

Dynamic Caching Behavior

When opening a table to use dynamic caching:

The fastest index is chosen automatically if none was specified during table open.

A partial cache is kept, ordered by index.

The cache contains the current cursor row, plus the last several rows fetched.

The cache is automatically refreshed ,with up-to-date data, when row navigation occurs and can be manually refreshed by calling the rowset's refresh( ) method.

The order in which a table can be navigated may be set via the rowset's indexName property.

Key-oriented operations, such as findKey( ) and setRange( ), can be used.

Basic Caching Behavior

Basic caching is used if:

The conditions for dynamic caching are not met

or

The usePassThrough property is set to False

With basic caching:

Every row fetched is cached on the workstation in case it is needed again.

The cache is not automatically refreshed. To refresh the cache you must call the query's requery( ) method or re-execute the query by setting the query's active property to False and then back to True.

The order rows are navigated must be set via the sql select statement's ORDER BY clause, rather than via the rowset's indexName property.

Key-oriented operations such as findKey( ) and setRange( ) are not available.

However, bookmarks can be used as long as rows can be uniquely identified.

Pros and Cons of Dynamic Caching

Dynamic caching works well with tables of up to a few million rows.

Larger tables may take a considerable amount of time to open.

Pros and Cons of Basic Caching

Basic caching can be used to quickly retrieve initial results from queries on large tables (tables with more than a few million rows) as long as no ORDER BY clause is included in the sql statement. However, you still need to be careful to limit the number of rows retrieved to the workstation, as every row retrieved is cached in workstation memory and can quickly use up available memory if the result set is more than a few million rows in size.