SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-60
Writing Efficient Programmatic Statements
Writing Efficient Programmatic Statements
When writing programmatic queries, you might have a choice between several
strategies for combinations of SELECT statements, cursor use, and update, delete,
and insert operations.
Single-Row and Multiple-Row SELECT Statements
A single-row SELECT statement is a request to return a single row to the host
program. This method is preferable to a cursor SELECT when only one row needs to
be retrieved, because
•
There are fewer executor calls
•
The disk process returns a single message instead of two messages when an
equality predicate is specified against columns of a unique index
•
The operation disables SBB so that unnecessary scanning and transfer to buffers
is avoided for the single row
Multiple-Row (Cursor) SELECT Statements
A multiple-row (or cursor) SELECT statement returns multiple rows one row at a time.
This technique is useful when retrieving multiple rows because
•
The cost of additional executor calls (for OPEN and CLOSE CURSOR) is spread
out over multiple fetches
•
SBB allows efficient scanning and transfer to the buffer for multiple-row access
Update and Insert Operations
To minimize executor calls and message traffic, use these guidelines when deciding
how to handle SELECT-with-update or SELECT-with-insert requests:
•
Choose a set update or delete instead of either
°
A single-row SELECT followed by an exact update or delete.
°
Multiple single-row SELECTS and subsequent exact updates or deletes. (Note,
however, that if you update large numbers of rows, lock escalations or
exceeded lock limits might cause aborts of transactions. In such a case,
consider committing a transaction after a certain number of updates or specify
ranges of rows in multiple set updates.)
°
A cursor SELECT and one or more UPDATE or DELETE WHERE CURRENT
operations (unless information must be examined prior to update).
•
In general, when selecting and updating a single row, choose a single-row
SELECT and exact update or delete over a single updatable cursor UPDATE or
DELETE WHERE CURRENT.