Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Key Problem

The latency we are experiencing isn't applying the changes from the Distributor to the Subscribers (if we watch Replication Monitor it's latency is usually <1 second), it is the time that a transaction committed to the publisher database takes to get to the distributor.

Part of the issue is that we have several filters applied to one of our most volatile tables, and on every update/insert/delete LogReader.exe needs to check which publication to put that record in based on any filters applied.

From tests we've done it seems that each additional filter that is applied it can exponentially increase the time that the LogReader takes to process each transaction.

(edit 23/06/2011: added more detail about the filters)

Background

In our setup of Replication we have several publication with filters on a highly volatile table (average of 1.5million transactions in a 2hr period). During heavy periods this can result in the LogReader running at a latency of 20 seconds (usually < 1 second)

We have identified several areas of improvement (reducing number of filters, reducing number of updates, farming out the processing etc..), one potential area of improvement is changing how the updates are applied.

An example table (to aid explanation)

myTable
----------------
myID         int
myGroupID1   int
myGroupID2   int
Suspended    bit
FilterFlag1  int
FilterFlag2  int
FilterFlag3  int

For replication to illustrate this we would have 5 publications of [myTable]:

Publication  Filter
-----------  ------
NoFilter1    [all records]*  
NoFilter2    [all records]*  
Filter1      FilterFlag1 = 1
Filter2      FilterFlag2 = 1
Filter3      FilterFlag1 = 1 AND FilterFlag2 = 1  
  • Other tables are combined into these publications which is why the same table with identical filters (none) are in more than one publication.

Current update process

The majority of applications that update this table do so by looping through their collection of objects, applying the change to that single object then committing their change to the database before moving onto the next object.

From a DB trace perspective this means we get up to 120 update statements when the change occurs

Example

update [myTable] set Suspended = @Suspended 
where myID = @pID

Proposed update process

As the object collections are actually based around the group ID's, one potential improvement is to do a bulk update (rather than individual updates) and then refresh the object collection. Reducing the number of update statements to 1 or 2 (depending on the business scenarios).

Example

update [myTable] set Suspended = @Suspended 
where myGroupID1 = @groupID1 
      and myGroupID1 = @groupID2

Impact on LogReader?

From an app processing perspective doing a single update makes sense to me (fewer round trips between the app and the database = quicker), however I'm not sure how the LogReader will treat both scenarios, as it needs to process each record updated by the transaction..

Will the LogReader process those records faster or slower on a bulk update?

share|improve this question

1 Answer 1

In the log records those are separate statements that are written to the log. The log reader would then batch those up and run them as a single transaction against the database.

If your database uses stored procedures, you can replicate the stored procedures, which would then allow the stored procedure call to be replicated instead of the actual data updates.

share|improve this answer
    
unfortunately we cannot feasibly replicate the various stored procedures and methods data is inserted/updated on that table. Additionally the current filter logic would then need to be put into the stored procedures.... –  Andrew Bickerton Jun 23 '11 at 7:11
    
Sounds then like you need to look at your distributor and see if hardware changes need to be made to the distributor to make the solution work as you need. Can you provide specs on your publisher and distributor? You'll want to look at your performance on the distributor and how long writes take. –  mrdenny Jun 23 '11 at 18:59
    
90% confident that it's not the writes on the distributor as we've done tests where the volume of writes are actually higher (removed all filters but same number of publishers), which resulted in the latency being lower (changes appeared on distributor quicker), the issue is definitely associated with that we've got multiple publications with filters (we are looking at ways we can reduce the number of filters), one of the proposed solutions is to change the updates to use bulk updates (hence my question). –  Andrew Bickerton Jun 24 '11 at 7:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.