Merge Replication increases the bulk insert time dramatically

You have triggers firing which populate the
MSmerge_contents table. You also have the merge agent
which tries to read this table. So, there are 2
additional features. You can't remove the triggers -
they're mandatory so try disabling the merge agent while
the bulk upload takes place. If this still takes a while,
try the same bulk load on an identical copy of the table
without the triggers - just to confirm that it is the
triggers and not the higher quantity of data that you now
have in the table, as placing rows according to the PK
value will by necessity take longer.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I checked, and we didn't have FIRE_TRIGGERS in our bulk insert commands. The
data was still being replicated, however.
Can you programatically disable the merge agent and then re-enable it during
bulk inserts? If so, can you point me to the right commands?
When we turn off replication, the insertion times remain around 3 seconds.
"Paul Ibison" wrote:

> You have triggers firing which populate the
> MSmerge_contents table. You also have the merge agent
> which tries to read this table. So, there are 2
> additional features. You can't remove the triggers -
> they're mandatory so try disabling the merge agent while
> the bulk upload takes place. If this still takes a while,
> try the same bulk load on an identical copy of the table
> without the triggers - just to confirm that it is the
> triggers and not the higher quantity of data that you now
> have in the table, as placing rows according to the PK
> value will by necessity take longer.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||There's something I don't understand here. When you did
the bulk insert without firing the triggers, how is it
possible that the data could be merged? Perhaps when you
say the data was still being replicated, you mean that
the merge agent was running? Anyway, What are the times:
(a) no replication
(b) replication and no firing of triggers and the merge
agent on a schedule which means it is not currently
running
(c) replication and firing of triggers and the merge
agent on a schedule which means it is not currently
running
(d) replication and firing of triggers and the merge
agent currently running
Rgds,
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul.
I do not understand either. However, the data is on both servers.
Here are some times:
a)No replication: average 1.5 seconds daily
d)Continuous Replication: 3,4,14,29,118 (averages of daily values over 5 days)
I will have to test the other two options.
I understand that adding RGUIDs will increase the time it takes to insert
data, but is an exponential increase expected?
"Paul Ibison" wrote:

> There's something I don't understand here. When you did
> the bulk insert without firing the triggers, how is it
> possible that the data could be merged? Perhaps when you
> say the data was still being replicated, you mean that
> the merge agent was running? Anyway, What are the times:
> (a) no replication
> (b) replication and no firing of triggers and the merge
> agent on a schedule which means it is not currently
> running
> (c) replication and firing of triggers and the merge
> agent on a schedule which means it is not currently
> running
> (d) replication and firing of triggers and the merge
> agent currently running
> Rgds,
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hi Paul,
It turns out our MSmerge_contents table was over 2.5 million records.
And it's not automatically clearing up. We are inserting data (not using
bulk insert) every five minutes, so this is a critical concern for us.
Could this be the problem? How can we tell it to automatically clean up
after data is replicated?
"Paul Ibison" wrote:

> There's something I don't understand here. When you did
> the bulk insert without firing the triggers, how is it
> possible that the data could be merged? Perhaps when you
> say the data was still being replicated, you mean that
> the merge agent was running? Anyway, What are the times:
> (a) no replication
> (b) replication and no firing of triggers and the merge
> agent on a schedule which means it is not currently
> running
> (c) replication and firing of triggers and the merge
> agent on a schedule which means it is not currently
> running
> (d) replication and firing of triggers and the merge
> agent currently running
> Rgds,
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>