Resolution:
There are the following options to increase the throughput of ADB by reducing database actions. It is recommended to first use the agent without those options and then apply those options one by one to improve the performance.

For publisher:
-max-row: Limits the number of rows per publish
-batch-pubstatus-updates: performs a bulk update in the publishing table based on the max-row value
-pub-batch-confirm-size: when published in RVCM, sets the number of messgaes to queue before updating the confirmation of receipt

For Subscriber:
-sub-batch-commit-size: the number of messages to batch before invoking a commit operation.
-sub-bulk-insert-size : queues insertion for a set number of rows; then performs a bulk insertion.

Scalability can also be improved by creating separate agents to do only publication or subscription, or even further, it can be improved by partitioning the subject so that each agent is only responsible for pub/sub to one table.

Listed below are a few pointers that could be considered for performance tuning of Adapter publisher. However, we suggest that you go through the Adapter User Guide to understand the different design time options and run time parameters that are available to improve performance. It is important to know that the performance tuning parameters or options vary with your requirements and design.

1. Publish by Value is quicker than Publish by Reference.

2. Because the Adapter Publisher can only run as a single threaded application, you may want to seperate the different Publication services into different Adapter instances.

3. Make sure that the tables (publication table, parent table and child tables) from which the Adapter is publishing have all the relevant indexes on them. Ensure that there are no full table scans occurring for queries issued by the Adapter.

4. The Adapter Publisher provides several parameter variables that can be set in the adapter tra file which can help improve performance. Some relevant parameters are discussed below.

a. adb.batchPubStatusUpdates on|off : When adb.PollingBatchSize is used, this optimizes publishing performance by batching message status updates to the publishing table. Do not use this option when messages are published using a parameterized subject name.

If an adapter instance stops before a batch update is performed, the status column is not updated. As a result, duplicate messages are published when the instance is restarted.

b. adb.groupsize : Specifies the number of rows to publish in a single message. Overrides the Group Size setting in the publication service configuration.

For further details on how Group messaging helps improve performance, please refer to the attached Adapter user guide.

c. adb.PollingBatchSize : Limits the amount of database records to be picked up in a polling cycle. The value indicates the number of parent rows to fetch for a poll interval. The default value, 0, indicates that all new rows should be fetched. Therefore, if there are too many unpublished parent records in the publication table, the Adapter might be overwhelmed if this parameter is set to default value.

d. adb.pubBatchConfirmSize : Applies only to publisher instances with publications that use certified message delivery. Optimizes performance by batching message status advisories to the publishing table. The value indicates the number of advisory messages to include in a single batch.

Do not use this option when messages are published using a parameterized subject name.

Note: If an adapter instance stops before a batch update is performed, the status column is not updated. As a result, messages that were successfully published might still have a status of P (pending) in
the publishing table when the adapter instance is restarted. In this case, the ledger file contains the correct status information. A smaller value for this property decreases this risk.

How do I improve the performance of ADB?

Product: TIBCO Adapter for ActiveDatabase Version: 3.3.1, 4.0.1, 4.1.0, 4.2.0, ALL OS: All --------------------