Hi There
WHen creating publciations under 2005 i saw a very interesting option under stament delivery, for inserts , updates , deletes there is an option that simply says insert/update/delete statement.
I could find very little in BOL about this under "Article Properties", is this what it soudns like ? FOr example if you say :
update Table set Cloumn = 'whatever', this will not trigger the update sp for each row at the subscriber , will it actually deliver the update statements and literally do the update/insert/delete statement at the subscriber?
Thanx
Yes. It is equivalent to supply value 'SQL' for parameter @.ins_cmd/@.upd_cmd/@.del_cmd in sp_addarticle. For more information, please take a look at the document for sp_addarticle http://msdn2.microsoft.com/en-us/library/ms173857.aspx.
Peng
|||just an FYI, using SQL instead of the custom stored procedures has a negative perf impact, it's primarily used for non-SQL server subscribers. Stick with the custom stored procs if you can.|||HI Greg
That is a very interresting point you bring up.
I was thinking of it exactly for performance, often in replication we perfom an update statement on the publisher that may take 10 minutes to execute and update say 2 millions rows. However this causes replication latency of up to half an hour at the subscriber at the execution of the MSupd_repl proc 2 million times is slow sometimes each execution of the proc can take a tenth of a second which is alot when you have millions of rows to update, performing the same update at the subscriber would take 10 minutes.
So i actually thought this option could help but apparantly not ?
|||Hi Dietz,
Change delivery method to "SQL" won't help the performance as replication will still deliver the changes row by row. It means that, in your example, we still call update 2 million times. To solve this problem, you can utilize the SQL 2005 functionality "Publishing Stored procedure Execution in Transactional Replication". More info can be found here (http://msdn2.microsoft.com/en-us/library/ms152754.aspx). It will require you to use stored procedures for your update though.
Peng
|||The performance you're seeing has nothing to do with SQL vs custom stored procs. In fact, if you used SQL, I bet it owuld be much slower since with SQL, multiple commands are batched then sent via one parameterized statement. With custom stored procs, the proc is compiled once, with SQL, you're pretty much guaranteed that no two batches will be the same, thus you'll have a ton of compilations.
What you're experiencing can be worked around by replicating the execuction of a stored procedure. http://msdn2.microsoft.com/en-us/library/ms152754.aspx.
|||Thanx Pend and Greg for the feedback, but how is this different from the usual method if it will still call update 2 millios times. DO you mean that instead of calling the MSupd sp on the sunscriber it delivers and update statement for each row?
I am aware of the sp publication method which was available in 2000 i thought this was somehow a similar concept. Thanx
|||If you issue a single update statement at the publisher that affects 2 million rows, you will get 2 million update statements, one for each row, replicated to the subscriber regardless whether you use SQL or custom stored procedures. If you do these batches often, not only can you experience long latency but your distribution database will fill up quickly. In these cases, it's recommended to replicate the execution of a stored procedure if latency and disk space is an issue. By putting your update statement in a proc, and replicating proc execution, rather than send 2 million deletes, it sends the proc call to the subscriber. Now you only have one command instead of 2 million.|||Hi Greg
Yes that is wagt i thought, however we are getting rid of replication, but good to know.
Thanx
