I got hired into a new company and they had merge replication already in place. They have had issues with it to the point I have to evaluate whether to keep it or replace it. The main issue is it takes many hours (over 12 hours in some cases) to insert or update a record. Honestly that is just unacceptable.
As an example I am trying to insert one record into a table (ComputerUsers) that links a laptop to a user. You only have two columns (one is call ComputerID and one is called UserID). There is a Computers table that stores the ComputerIDs and the Computername. There also is a Users table that stored the UserID and username.
All I am trying is insert one record into the ComputerUsers table. Last time I ran it it took over 13 hours. This table is published and it is part of the Dynamic Filter.
I have tried everything I have read online. I created two separate indexes for the UserID and ComputerID columns since they are both included in separate joins in the Filter. There is also a clustered primary key on the Userid and ComputerID columns since the combination of the two are unique (A computer can be assigned to two users or vica versa). I have also have tried rebuilding the indexes and even created some suggested indexes on the merge replication tables.
I am at my wit's end. What am I missing? I find it hard to believe this is normal for Replication because I doubt anyone would use it if it was. Thanks.
Best Answer
I had a very similar sounding problem. We replicated a table with millions of rows. One of the subscribers would take up to 2 minutes to insert 100 new rows.
I solved the issue by adding an index to the rowguid column of the table it was inserting to. I know this sounds like it should degrade performance but it looked like it was doing a full table scan before inserting, probably to make sure a duplicate row was not inserted.
Once I added this index replication was running smoothly again