|
This section presents a checklist for designing high performance SQL Remote installations.
Keep the number of publications small In particular, try not to reference the same table in many different publications.
The work the database server needs to do is proportional to the number of publications. Keeping the number low and making effective use of subscriptions lightens the load on the database server.
When operations occur on a table, the database server and the Message Agent must do some work for each publication that contains the table. Having one publication for each remote user will drastically increase the load on the database server. It is much better to have a few publications that use SUBSCRIBE BY and have subscriptions for each remote user. The database server does no additional work when more subscriptions are added for a publication. The Message Agent is designed to work efficiently with a large number of subscriptions.
Group publications logically For example, if there is a table that every remote user requires, such as a price list table, make a separate publication for that table. Make one publication for each table where the data can be partitioned by a column value.
Use subscriptions effectively When remote users receive similar subsets of the consolidated database, always use publications that incorporate SUBSCRIBE BY expressions. Do not create a separate publication for each remote user.
Pay attention to Update Publication Triggers In particular:
Use the NEW / OLD SUBSCRIBE BY syntax.
Tune the SELECT statements to ensure they are accessing the database efficiently.
Monitor the transaction log size The larger the transaction log, the longer it takes the Message Agent to scan it. Rename the log regularly and use the delete_old_logs option.