SQL Remote User's Guide
Principles of SQL Remote Design
Each time a row in a table is inserted, deleted, or updated, a message has to be sent to those subscribed to the row. In addition, an update may cause the subscription expression to change, so that the statement is sent to some subscribers as a delete, some as an update, and some as an insert.
For details of what statements get sent to which subscribers, see How statements are replicated. For details on subscriptions, see the following two chapters.
This section describes how SQL Remote sends the right operations to the right recipients.
The task of determining who gets what is divided between the database server and the Message Agent. The engine handles those aspects that are to do with publications, while the Message Agent handles aspects to do with subscriptions.
Adaptive Server Anywhere evaluates the subscription expression for each update made to a table that is part of a publication. It adds the value of the expression to the log, both before and after the update.
Not the subscriber listAdaptive Server Enterprise does not evaluate or enter into the log a list of subscribers. The subscription expression (a property of the publication) is evaluated and entered. All handling of subscribers is left to the Message Agent. |
For a table that is part of more than one publication, the subscription expression is evaluated before and after the update for each publication.
The addition of information to the log can affect performance in the following cases:
Expensive expressions When a subscription expression is expensive to evaluate, it can affect performance.
Many publications When a table belongs to many publications, many expressions must be evaluated. In contrast, the number of subscriptions is irrelevant.
Many-valued expressions Some expressions are many-valued. This can lead to much additional in formation in the transaction log, with a corresponding effect on performance.
In a SQL Remote for Adaptive Server Enterprise publication, the subscription expression must be a column. The subscription column contains either a single value or a comma-separated list of values.
Not the subscriber listAdaptive Server Enterprise does not enter into the log a list of subscribers. The column value is entered. All handling of subscribers is left to the Message Agent. |
When a table is marked for replication using sp_add_remote_table (which calls sp_setreplicate), Adaptive Server Enterprise places an entire before image of the row in the transaction log for deletes, and entire after image for inserts, and both images for updates. This means that the before and after values of the subscription column are available.
The Message Agent reads the evaluated subscription expressions or subscription column entries from the transaction log, and matches the before and after values against the subscription value for each subscriber to the publication. In this way, the Message Agent can send the correct operations to each subscriber.
While large numbers of subscribers do not have any impact on server performance, they can impact Message Agent performance. Both the work in matching subscription values against large numbers of subscription values, and the work in sending the messages, can be demanding.