SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-63
Batch Considerations
Batch Considerations
Batch operations imply queries that process large amounts of data in a sequential
order.
Use of these can improve batch performance:
•
Block buffering, described in Reducing Messages With Buffering Options on
page 4-21.
•
Parallel sorts to increase speed and balance processor and disk use.
•
Parallel processes to even out workload and make the system easier to balance.
•
Multiple spooler processes so that parallel batch processes can write to multiple
spoolers.
•
Key-sequenced tables with indexes to avoid sorts.
These guidelines apply to update operations:
•
Whenever an insertion, update, or deletion occurs within a process that has an
open cursor (whose execution plan uses sequential block buffering (SBB) to
access the same table), perform the update or delete with the same cursor (use
the WHERE CURRENT clause). If you do not follow this guideline, virtual
sequential block buffering (VSBB) is used for the SELECT with the UPDATE,
invalidating that buffer, which can be very expensive. CONTROL TABLE
tablename SEQUENTIAL READ OFF cannot be used to avoid this situation.
For more information about SBB and VSBB, see Section 4, Improving Query
Performance With Environmental Options.
•
Use cursors for UPDATE with EXCLUSIVE when you know you will update the row
and then UPDATE WHERE CURRENT. Otherwise, you can use a single SELECT.
•
Use BEGIN WORK and COMMIT WORK only for update transactions; otherwise,
you incur unnecessary overhead in the audit trails. A start and stop transaction
audit record is written unnecessarily to the audit trail, and TMF maintains a
transaction reference throughout the execution of the transaction.
•
Commit as soon as possible when finished. Note that you might need extra
commits to avoid a lock limit or lock escalation.
•
If batch updates occur during online processing, define short transaction intervals
so that rows are not locked for long periods of time. If updates are large, consider
dividing them into smaller updates and committing transactions at a frequency
based on concurrency issues and transaction limits. For example, you can commit
transactions based on number of updates or after a fixed length of time.