SQLCache database parameter
Description
Specifies the number of SQL statements
that PowerBuilder should cache. The default is 0, specifying an empty SQL cache.
PowerBuilder caches:
-
SQL statements generated
by a DataWindow object or report -
Embedded SQL statements
Controls
-
ODBC
Syntax
|
1 |
SQLCache=<span>value</span> |
|
Parameter |
Description |
|---|---|
|
value |
The number of cursors you want to open |
Default
SQLCache=0
Usage
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, PowerBuilder 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, PowerBuilder 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
Examples
To set the SQL cache
size to 25 statements:
-
Database
profileType
25in the Number
Of SQL Statements Cached box
on the Transaction page in the Database Profile Setup dialog box. -
Application
Type the following in code:
1SQLCA.DbParameter="SQLCache=25"