Specifies the number of SQL statements that DataWindow Designer should cache. The default is 0, specifying an empty SQL cache.
DataWindow Designer caches SQL statements generated by a DataWindow object.
ODBC
SQLCache = value
Parameter |
Description |
|---|---|
value |
The number of cursors you want to open in a script, plus the number of DataWindow-generated SELECT statements with retrieval arguments (default = 0). |
SQLCache = 0
The SQLCache parameter is present in the Database Profile
Setup dialog box for Oracle connections. However, for Oracle 8.x
and later, Oracle OCI handles caching and setting SQLCache is not
recommended.
Maintaining statements in the cache Statements in the SQL cache are maintained on a least-recently-used (LRU) basis. In other words, if a statement must be removed from the cache to make room for another statement, DataWindow Designer removes the statement that was least recently executed.
SQLCache and bind variables Caching SQL statements that you execute frequently improves their performance. Statements with bind variables are often the most frequently used. In fact, if your DBMS does not support bind variables, caching statements is of limited value.
Setting DisableBind to use cached statements In order to use cached statements, make sure the DisableBind parameter is set to 0 (the default). This enables the binding of input variables to SQL statements.
For more about using bind variables, see DisableBind.
What happens The first time you execute a SQL statement containing bind variables, the DataWindow server does the following in this sequence:
Parses the statement.
For SQL SELECT statements, calls the appropriate database function to get a description of the result set.
Allocates memory buffers for the bind variables.
Binds the allocated memory buffers to the parsed statement.
When you cache this SQL statement, the DataWindow server stores the parsed statement, result set description, and memory buffer allocation and binding in the SQL cache. The next time you execute this statement, DataWindow Designer finds it in the cache and avoids the overhead of repeating these steps.
If the DataWindow server finds an exact match for this statement in the SQL cache, it simply copies the new values supplied for the bind variables to the preallocated memory buffers and executes the statement. This is much faster than having to process the statement from scratch.
Determining the size of your SQL cache To determine an appropriate size for your SQL cache, you can check the value of the SqlReturnData property of the Transaction object.
When you disconnect from the database, the number of hits, misses, and entries in the SQL cache is stored in SqlReturnData as follows:
Hits The number of times the DataWindow server found a matching statement in the SQL cache
Misses The number of times the DataWindow server did not find a matching statement in the cache
Entries The total number of statements in the SQL cache, which is determined by your SQLCache setting
To set the SQL cache size to 25 statements:
Database profile Type 25 in the Number
Of SQL Statements Cached box on the Transaction tab in the Database
Profile Setup dialog box.
Application Type the following in code:
SQLCA.DbParameter = "SQLCache=25"
