StarQuest Technical Documents

Improving SQDR Performance

Last Update: 07 February 2005
Product: StarQuest Data Replicator
Version: 3.0
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 using the new feature in SQDR 3.0 of 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.

Source/Destination Host Tuning

  • If you are connecting to your host though Microsoft SNA Server, you may be able to dramatically improve performance by customizing Microsoft SNA Server for your environment. This involves tuning the RU size, Pacing values and Frame sizes, etc. Please see our technical document Technical Document SQ000I0094, Performance Tuning of Microsoft SNA Server for use with StarSQL.

  • 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.

DISCLAIMER

The information in technical documents comes without any warranty. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The information in technical documents may be gathered from various sources, including IBM, Microsoft, and other organizations.