HP 3000 Manuals

ALLBASE/SQL Performance and Monitoring Guidelines

General Tips on Managing Transactions 

   *   Non-cursor SELECT involves less overhead than FETCH with a cursor.
       Use SELECT or BULK SELECT if you have a choice. 

   *   Use the KEEP CURSOR option with the OPEN statement to permit
       frequent release of locks during long cursor operations.  A
       combination of Cursor Stability and KEEP CURSOR can improve
       concurrency by letting you scan and update a large table without
       holding locks for the duration of the entire scan.  (Do not forget
       to use the COMMIT WORK statement immediately following an OPEN for
       a kept cursor.) 

   *   Use DML only mode (i.e., DDL Enabled set to NO through SQLUtil) to
       improve concurrency on access to system catalog tables. 

   *   Try to perform data definition in single-user mode as much as
       possible.  Data definition involves such activities as CREATE,
       DROP, ALTER, UPDATE STATISTICS, GRANT, and REVOKE. It is advisable
       to perform these operations outside of a production application if
       at all possible, since these operations place exclusive locks on
       the system catalog tables.  These locks, like all other exclusive
       locks, are held for the duration of the transaction, and will
       reduce concurrency as other users attempt to access the system
       catalog tables.  For more information on system catalog locks,
       refer to the appendix, "Locks Held on the System Catalog," in the
       ALLBASE/SQL Database Administration Guide.

Using Short Transactions and Savepoints 

At the end of a transaction, the COMMIT WORK statement makes changes
permanent to disk, which causes I/O. Short transactions free locks and
buffers more frequently, which improves concurrency, but they also
increase log I/O (the increase is slight, since the use of group commits
in logging dilutes the effect of increased log I/O in this case).  Longer
transactions minimize I/O, but they hold locks longer and thus reduce
concurrency.  In general, you should keep your transactions as short as
possible to improve the performance of the DBEnvironment.

A savepoint, created in a transaction with a SAVEPOINT statement, marks a
place you can roll back to in the transaction, releasing locks that were
obtained since you issued the statement.  Savepoints can be used to
reduce the number of transactions that must be resubmitted because part
of the transaction was unsuccessful.


NOTE  Whether or not you use savepoints, the entire transaction is 
      rolled back in the event of a deadlock.

MPE/iX 5.0 Documentation