SQL/MX Queuing and Publish/Subscribe Services

Performance Considerations
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
4-2
Considerations for Embedded UPDATEs
feature. For information about the TMF autoabort feature, see the TMF Reference
Manual.
Considerations for Embedded UPDATEs
•
Be aware of a performance issue with embedded UPDATEs.
You might want to use publish/subscribe services to model a queue, so that a
published row is returned to only one subscriber. To do this, you can use an
embedded UPDATE statement, as described in Section 2, Major Queuing
Features. The subscribers can scan a stream with a selection predicate on a
special column that might be called a “WORK_PENDING” column. This column
marks whether the row, which represents a piece of work for the application, has
been processed. The SET clause of the embedded UPDATE sets it to TRUE.
Publishers inserting or updating the row set the column to FALSE.
One problem with this type of application is that, even if there is an index on the
WORK_PENDING column, SQL/MX will not select rows for update based on a
scan of the index. And because the application updates the WORK_PENDING
column, it is not possible to cluster the base table by this column. The result is that
a full table scan can be performed to look for the (potentially few) rows that have
the WORK_PENDING column set.
•
Use embedded DELETEs instead of embedded UPDATEs.
As an alternative to the embedded UPDATE and a WORK_PENDING column,
consider using a separate table to track the pending rows in a master table. This
table can be called WORK_PENDING_QUEUE. Each row in
WORK_PENDING_QUEUE would represent a row in the master table for which
work is pending. The only column in the WORK_PENDING_QUEUE is a row
identifier used as a foreign key to match to the primary key in the master table. If
SQL/MX tables are used, the referential integrity feature can be used to enforce
the constraint that all rows in the WORK_PENDING_QUEUE have a matching row
in the master table. If SQL/MP is used, the application must be written to enforce
this constraint. The statements to create the SQL/MX tables are:
create table master ( a int not null, b int, c int, primary key(a));
create table work_pending_queue( rowid int ,
foreign key (rowid) references master(a)
on update restrict
on delete restrict );
The publisher can inform the subscriber of new work in the master table by
inserting the master row’s primary key value into the WORK_PENDING_QUEUE
table. If SQL/MX tables are used, the insertion into WORK_PENDING_QUEUE
can be handled by the system, through the use of triggers. For example, this
trigger could be defined:
create trigger make_work_pending
after insert on master referencing new as newrow
for each row insert into work_pending_queue values ( newrow.a );