SQL Server Performance Forum – Threads Archive
Update only changed columns to improve replication
I wanted to know if someone ran into this topic before and comments can help. I’m using SQL Server replication with takes Column changes (not the whole row). My problem is that the entire application was built using Stored Procedures which updated ALL columns when a user called them. Example: ===================== CREATE PROC Document_UP@Id_Document uniqueidentifier,
@Id_DocumentType int,
@Branch int,
@Code int,
@DateTime datetime,
@Id_Status int,
@Enabled bit ,
@Id_Employee as uniqueidentifier AS UPDATE Tb_Document
SET Id_DocumentType = @Id_DocumentType,
Branch = @Branch,
Code = @Code,
DateTime [email protected],
Id_Status = @Id_Status,
Enabled = @Enabled
WHERE(Tb_Document.Id = @Id_Document) GO ======================================== It worked perfect until now that we start using replication. In fact i’d to know if i can improve the stored in some smart way so that the stored itselfs UPDATE ONLY COLUMNS WITH CHANGED DATA. If thats noy possible…i’ll have no choise than build the Update statement dynamicaly in the Source Code of the App. Well, thanks in advance for any helps or comments. Gerardo
Hi Gerardo, I think the only option to try to reduce the replication overhead is to build the update statement dynamically
OR
get the application to tell the proc which columns are to be updated and have an if…else statement inside the procedure to deal with common sets of columns Sorry no magical answer… What problems are you finding with replication? It may be that row level change tracking is more suitable for you? It reduces the burden on the publisher, but increases the burden on the network link between publisher and distributer as well as distributer and subscribers… Cheers
Twan
Twan, i’m not finding any problems with replication… i just want to reduce Conflicts when two users edit the same record (row). The overhead across the network is not a problem in this scenario. I think my only way out is just to make some dynamic code on the Stored Proc side. Btw, any smart ideas of how to: a) Make a query inside the stored to obtain data stored in DB
b) Compare it with parameters passed in Stored.
c) Build dynamic code for the update statement. I have some ideas of how to resolve this, but maybe someone else already solved it in only some lines of code and that will be pretty good. Thanks for your comment, Gerardo
]]>