June 18, 2008 at 3:16 pm
What if a transaction consists of 1,000,000 row changes? Do commands start getting applied when the transaction from the publisher commits? i would say yes, I'm watching replication monitor and have 3.4 million uncommited commands and I know a developer issue 1 UPDATE affecting millions of rows.
Do we can performance with replication by having developers commit smaller amounts of row changes at a time? My guess is : Yes.
June 19, 2008 at 5:30 am
You have not specified the type of replication, but I will assume transactional replication by your description so far.
"Transactional" actually refers to the fact that the replication agent is actually reading the transaction log on your publisher. Since nothing goes to the transaction log until it is committed, you are correct in assuming that a transaction including 1m rows will not start replicating until the entire transaction is committed.
Here is the next kicker - the 1m row update that your application just did is replicated (assuming you are using the defaults or something close for replication) as 1m individual updates - or if you have updated a column that has a unique constraint, 1m deletes and 1m inserts (deferred updates). So, very large transactions can have a serious performance implication on transactional replication.
One very viable solution is to keep transaction size down - this is recommended in replication scenarios. Also, avoid unnecessary transactions - updating a column to it's original value still replicates an update.
Another solution, assuming you are using stored procedures for updates to your publisher, is to switch replication to replicating the procedure calls. This way, if you have a single procedure call that updates 1m records, the procedure will be called on the subscriber rather than 1m individual updates. This does get into a much more complicated area of replication, so I would recommend getting a book or taking a class before getting into something like this.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply