Chapter 8. SQL Remote Design forAdaptive Ser verEnter prise
Ifyou are extracting databases for many users, and performance is a problem
foryou, you can use a subscription view toimprove performance. The view
mustcontain a subquery, which is used for extraction and synchronization
only,and is ignored during log scanning. The tables involved still need to
havetriggers defined to maintain the
subscription-list
column.
❖Tocreate a subscription view
1. Design a query that uses a subquery to select the proper rows for a
subscriptionfrom a table.
Forexample, continuing the example from the preceding sections, the
followingquery selects the rows of the Contact table for a user
subscribedby 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 Customer_sub_view AS
SELECT *
FROM dbo.Customer
WHERE ’repxx’ IN ( SELECT rep_key
FROM dbo.Policy
WHERE dbo.Policy.cust_key = dbo.Customer.cust_key )
Inthis view definition, it does not matter what value you use on the
left-handside of the WHERE clause (repxx in the example above). The
replicationtools use the subquery for extraction and synchronization
only.Rows for which the SUBSCRIBE BY value is in the subquery
resultset 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_article SalesRepData,
’Customer’,
NULL,
’subscription_list’,
’Customer_sub_view’
Thesubscription_list column is used for log scanning and the subquery is
usedfor extraction and synchronization.
☞Formore information, see “Tuning extraction performance” on
page155,“sp_add_article procedure” on page 381, and
“sp_modify_articleprocedure” on page 398.
163