| Home | Previous Lesson: System Options Next Lesson: Syntax Options |
Options under Transactions tab page are important. Asynchronous option allows you to do other tasks�such as retrieving from other database using another connection, executing non-database command in PB or switching to other Windows application, etc�while PowerBuilder is retrieving data from one database connection. When you check this option, you may also want to specify the number of seconds PowerBuilder should wait for the database response to your query. PowerBuilder waits indefinitely if you specify zero.
Options under Caching group specifies how rows/SQL statements PowerBuilder should cache. All these options affect performance of your application. The 'Retrieve Blocking Factor' specifies the number of rows PowerBuilder should fetch at a time. The next option specifies how many rows Data Pipeline should buffer before it inserting. The next option 'Number of SQL Statements Cached' is self-explanatory. The default is zero. The first time you execute a SQL statement containing bind variables, PowerBuilder does the following in this sequence:
When a non-zero value is specified for the 'Number of SQL Statements Cached' option, PowerBuilder stores the parsed statement, result set description, and memory buffer allocation and binding in the SQL cache. Statements in the SQL cache are maintained on a least-recently-used (LRU) basis, meaning, if a statement must be removed from the cache to make room for another statement, PowerBuilder removes the statement that was least recently executed. Next time when you execute a SQL statement, PowerBuilder checks the SQL cache and if it finds one, then it simply copies the new values supplied for the bind variables to the pre-allocated memory buffers and executes the statement. This is much faster than having to process the statement from scratch.
In order to take advantage of SQL caching, the ODBC drive and the back-end database should support SQL caching and also make sure you uncheck DisableBind option.
Cursor Library under Cursors group allows you to specify which cursor library to use, i.e., ODBC cursor library or the native database cursor library. Other options under this group are advanced and are explained in 'Embedded & Dynamic SQL' session in detail.
StaticBind, which is on by default, doesn't retrieve result set description to validate before retrieving the data. This makes the retrieval process faster.
Qualify Stored Procedure with Owner Name: In Sybase, if you do not qualify the stored procedure name, Sybase first checks whether the user owns one with that name. If not, it tries to execute owned by the database owner. If not found, then it looks in the SybSystemProcs database and executes it if it finds one. However, if you qualify the store procedure name, then it tries to execute exactly as specified and raises error if not found. The behavior may be different between different database vendors.
| Home | Previous Lesson: System Options Next Lesson: Syntax Options |