I currently have a highly customized synchronization solution which uses MSF. The general architecture of the solution is that data is synchronized between 2 sites via a web service. The sites have different db schema, so everything is sync'd at an object
level. NHibernate is used for data access and change tracking is done inline when a change is made to the business objects right before the object change is flushed to the database.
Inline change tracking like this is very reliable and has no latency, however, the performance seriously decreases when large batches of updates are performed. I have found that the bottleneck is when I increment the tick count, which requires an atomic
operation to read the value then increment it. The inline change tracking takes 10-15x as long, which isn't a big deal when a user is just changing a few records, but when there is a bulk change with thousands of records, it is really noticeable.
I tried using the SQL Server Service Broker to asynchronously queue the changes so the tracking doesn't block the application, but this scenario offered no net gains in performance since the Service Broker writes the message to an internal queue table before
it processes it.
I am trying to stay away from table level tracking, as the synchronization is done at the business object level. Additionally, I suspect that using SQL Server Change Tracking has a similar performance overhead.
Has anyone had a similar performance issue with change tracking? I would greatly appreciate any thoughts or feedback about my dilemma.
Here is the T-SQL that I have found to be the bottleneck. It doesn't seem to be anything remarkable, but if you loop this 5000 times you will see that the time adds up fast.
BEGIN TRANSACTION
BEGIN TRY
--@ReplicaID is uniqueidentifier passed in from the client
DECLARE @NewTickCount bigint
SELECT @NewTickCount = TickCount
FROM SynchronizationFMServerReplicas
WHERE ReplicaID = @ReplicaID
SET @NewTickCount = @NewTickCount + 1
UPDATE SynchronizationReplicas
SET TickCount = @NewTickCount
WHERE ReplicaID = @ReplicaID
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
END CATCH
IF @@TRANCOUNT > 0 COMMIT TRANSACTION
Thanks in advance for your thoughts.
Regards,
Trevel Beshore
