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)
>
Showing posts with label populate. Show all posts
Showing posts with label populate. Show all posts
Monday, March 12, 2012
Monday, February 20, 2012
Merge replication and DTS
Hello,
I would like to use merge replication to populate data
into a table based on criteria...
How do I incorporate a where clause in the replication?
Is there a way to incorporate DTS packages in replication?
Thanks,
niv
Niv,
transformable subscriptions are possible, however they are a big overhead
and may not be required for your needs. When you say a where clause, this
corresponds to a horizontal filter in replication terms. Have a look at the
filter rows tab in the publication properties and you'll have the option of
entering static of dynamic filters there.
HTH,
Paul Ibison
|||Testing, 1,2,3...
Paul
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I would like to use merge replication to populate data
into a table based on criteria...
How do I incorporate a where clause in the replication?
Is there a way to incorporate DTS packages in replication?
Thanks,
niv
Niv,
transformable subscriptions are possible, however they are a big overhead
and may not be required for your needs. When you say a where clause, this
corresponds to a horizontal filter in replication terms. Have a look at the
filter rows tab in the publication properties and you'll have the option of
entering static of dynamic filters there.
HTH,
Paul Ibison
|||Testing, 1,2,3...
Paul
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Subscribe to:
Posts (Atom)