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
Applies to
ODBC
Syntax
|
1 |
SQLCache=value |
|
Parameter |
Description |
|---|---|
|
value |
The number of cursors you want to open in a script, |
Default value
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, PowerBuilder 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, PowerBuilder 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 PowerBuilder 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 PowerBuilder found a matching statement in
the SQL cache -
Misses
The number of times PowerBuilder 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 profile
Type 25 in 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.DBParm="SQLCache=25"
See also