SQL Remote User's Guide
  SQL Remote Design for Adaptive Server Enterprise
    Partitioning tables that do not contain the subscription column

Tuning extraction performance

When extracting or synchronizing a user, the subscription-list column can cause performance problems as it necessitates a full table scan.

If you are extracting databases for many users, and performance is a problem for you, you can use a subscription view to improve performance. The view must contain a subquery, which is used for extraction and synchronization only, and is ignored during log scanning. The tables involved still need to have triggers defined to maintain the subscription-list column.

To create a subscription view

1. Design a query that uses a subquery to select the proper rows for a subscription from a table.

   For example, continuing the example from the preceding sections, the following query selects the rows of the Contact table for a user subscribed by rep_key value rep5:

   SELECT *
   FROM Contact
   WHERE 'rep5' = (SELECT rep_key
                  FROM Customer
                  WHERE cust_key = Contact.cust_key )

2. Create a view that contains this subquery. For example:

   CREATE VIEW Contact_sub_view AS
   SELECT *
   FROM dbo.Contact
   WHERE 'repxx' = ( SELECT rep_key
      FROM dbo.Customer
      WHERE cust_key = dbo.Contact.cust_key )

   In this view definition, it does not matter what value you use on the left-hand side of the WHERE clause (repxx in the example above). The replication tools use the subquery for extraction and synchronization only. Rows for which the SUBSCRIBE BY value is equal to the subquery result set are extracted or synchronized.

3. Give the name of the view as a parameter to sp_add_article or sp_modify_article:

   exec sp_add_remote_table 'Contact'
   go
   exec sp_add_article SalesRepData,
                     'Contact',
                     NULL,
                     'subscription_list',
                     'Contact_sub_view'

   The subscription_list column is used for log scanning and the subquery is used for extraction and synchronization.

   For more information, see Tuning extraction performance for shared rows, sp_add_article procedure, and sp_modify_article procedure.