Sunday, June 12, 2011

Stored Procedure Execution Article

If there are transactional replication publication articles that update a large number of rows using a stored procedure, you may want to consider publishing the stored precedure as a stored procedure execution article to perform those updates.  The typical example is to update every item's price by 10%.

CREATE PROCEDURE UpddateItemPriceTenPct AS
UPDATE ItemInfo SET Price = Price * 1.10

In this example, each row is updated and is transmitted to the target subscribers.  With a large number of rows, it can put a burden on performance. 

If this is a stored procedure execution article, then just the stored procedure execution gets transmitted to each subscriber who updates their rows locally.

Technet gives details here.

No comments:

Post a Comment