StarQuest Technical Documents

Improving SQDR Performance

Last Update: 9 November 2017
Product: StarQuest Data Replicator
Version: 3.75
Article ID: SQV00DR010

Abstract

The overall performance of SQDR depends upon many factors such as the system specifications, network capacity and available database host resources. Use this document to help tune SQDR and the system environment to maximize performance.

Solution

Grouping Subscriptions in SQDR

You can realize significant performance improvement by grouping subscriptions. Grouping subscriptions provides for high speed replication because you can run subscriptions concurrently to take advantage of multi-threading. With multi-threading the Data Replicator can continue fetching data even if another subscription in the group is idle while it waits for an operation to complete. See the SQDR help topic "Creating a Group of Subscriptions" for more information.

SQDR ODBC Commit Level

Use an ODBC commit interval of either 0 (zero) or a large value such as 20,000. A value of 0 means that SQDR will commit all rows when it reaches the end of the table. To change this value, right-click on the SQDR service in the SQDR Manager and select Properties. In the Global tab, edit the ODBC Commit Interval.

Multirow Fetch and Insert

SQDR can deliver enhanced performance for large data transfers by using multirow fetch and insert, when supported by the source and destination ODBC drivers. Multirow fetch and insert behavior is enabled by default in SQDR 3.75 and later. Using Data Replicator Manager, select the Properties of the service and examine the current setting. If the setting is unchecked, before enabling it we recommend examining your environment to understand why it may have been disabled in the past.

Source/Destination Host Tuning

• If the source or destination database exists on an AS/400 iSeries host, try tuning the system to improve performance. See Technical Document SQV00SQ005, AS/400 iSeries tuning tips to improve performance when using StarSQL.

• If the destination database is on a SQL Server server, you may be able to improve performance of bulk copy replications by enabling the Select Into / Bulk Copy option of the database properties. This option allows non-logged operations to be performed and specifies the type of recovery model for the database. Refer to the SQL Server Books Online or other SQL Server documentation for more information about setting database properties and using recovery models.