SQL/MX Queuing and Publish/Subscribe Services

Performance Considerations
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
4-3
Considerations for Rowsets
When the subscriber processes the work, it deletes the row from the
WORK_PENDING_QUEUE with an embedded DELETE. This row can be joined to
the master table with this syntax:
select a, b, c from
(delete from stream(work_pending_queue)) as q,
master m
where q.rowid = m.a;
Considerations for Rowsets
•
Use rowsets with destructive SELECTs.
Embedded UPDATEs and DELETEs typically send and receive one file system
message per SQL fetch operation. Rowsets can be used with destructive
SELECTs to amortize the costs of the file system message among many rows.
Each fetch into the rowset can return many rows with a single file-system
message.
•
Use a rowset size that is slightly larger for the subscriber than for the publisher.
Optimal performance can be achieved by basing the size of the subscriber’s
rowset on the size of the publisher’s transaction. Specifically, the rowset should be
at least one element larger than the number of rows published in the publisher’s
transaction. Then, with a single file system message, the SQL executor can return
all available rows. Because it still has not filled the entire rowset, the executor will
not need to send another file system message to discover that there are no more
rows to fetch.
Considerations for Embedded DELETEs
•
Use a dummy row to prevent destructive SELECTs from causing a table to be
emptied.
When a partition goes from empty to nonempty (and back), extra processing is
used. Performance problems can occur for applications that use embedded
DELETEs. You can avoid this problem by keeping one dummy row per partition.
Selection predicates on the embedded DELETE prevent selecting the dummy row.
You can hide the added complexity of this dummy row from day-to-day application
coding by using a protection view (for SQL/MP-based tables) or a view (for
SQL/MX tables). The view can be used to avoid the row so that the embedded
DELETE can just access the view.