none
Sync Framework 2.1. Bulkupdate number of rows fixed at 75 rows

    Question

  • I am syncing a table orders with 100000 rows. When all rows are changed, I find in the sync tracefile during the update of the peer a call to the orders_bulkupdate SP for every 75 rows repeated. Is this value (75) fixed or can it be configured? Why are 100000 rows for a bulk update splitted in 75 rows packets?

    INFO   , w3wp, 10, 06/17/2013 14:22:45:932,    ----- Updates for Table "orders" -----
    VERBOSE, w3wp, 10, 06/17/2013 14:22:45:963,    Executing Command: [orders_bulkupdate]
    VERBOSE, w3wp, 10, 06/17/2013 14:22:45:979,       Parameter: @sync_min_timestamp Value: 62242
    VERBOSE, w3wp, 10, 06/17/2013 14:22:46:182,       Parameter: @sync_scope_local_id Value: 1
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:151,       Parameter: @changeTable Value: orders
    INFO   , w3wp, 10, 06/17/2013 14:22:48:495, Applied 75 of 75 rows with bulk command BulkUpdateCommand
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:510,    Executing Command: [orders_bulkupdate]
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:557,       Parameter: @sync_min_timestamp Value: 62242
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:557,       Parameter: @sync_scope_local_id Value: 1
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:557,       Parameter: @changeTable Value: orders
    INFO   , w3wp, 10, 06/17/2013 14:22:48:557, Applied 75 of 75 rows with bulk command BulkUpdateCommand
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:573,    Executing Command: [orders_bulkupdate]
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:573,       Parameter: @sync_min_timestamp Value: 62242
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:573,       Parameter: @sync_scope_local_id Value: 1
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:573,       Parameter: @changeTable Value: orders
    INFO   , w3wp, 10, 06/17/2013 14:22:48:588, Applied 75 of 75 rows with bulk command BulkUpdateCommand
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:588,    Executing Command: [orders_bulkupdate]
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:588,       Parameter: @sync_min_timestamp Value: 62242
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:588,       Parameter: @sync_scope_local_id Value: 1
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:588,       Parameter: @changeTable Value: orders
    INFO   , w3wp, 10, 06/17/2013 14:22:48:604, Applied 75 of 75 rows with bulk command BulkUpdateCommand
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:604,    Executing Command: [orders_bulkupdate]
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:604,       Parameter: @sync_min_timestamp Value: 62242
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:604,       Parameter: @sync_scope_local_id Value: 1
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:604,       Parameter: @changeTable Value: orders
    INFO   , w3wp, 10, 06/17/2013 14:22:48:620, Applied 75 of 75 rows with bulk command BulkUpdateCommand
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:620,    Executing Command: [orders_bulkupdate]
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:620,       Parameter: @sync_min_timestamp Value: 62242
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:635,       Parameter: @sync_scope_local_id Value: 1
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:635,       Parameter: @changeTable Value: orders
    INFO   , w3wp, 10, 06/17/2013 14:22:48:635, Applied 75 of 75 rows with bulk command BulkUpdateCommand
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:635,    Executing Command: [orders_bulkupdate]
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:651,       Parameter: @sync_min_timestamp Value: 62242
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:651,       Parameter: @sync_scope_local_id Value: 1
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:651,       Parameter: @changeTable Value: orders
    INFO   , w3wp, 10, 06/17/2013 14:22:48:651, Applied 75 of 75 rows with bulk command BulkUpdateCommand
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:667,    Executing Command: [orders_bulkupdate]
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:667,       Parameter: @sync_min_timestamp Value: 62242
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:667,       Parameter: @sync_scope_local_id Value: 1
    VERBOSE, w3wp, 10, 06/17/2013 14:22:48:667,       Parameter: @changeTable Value: orders
    INFO   , w3wp, 10, 06/17/2013 14:22:48:682, Applied 75 of 75 rows with bulk command BulkUpdateCommand
    etc. etc.

    Monday, June 17, 2013 2:54 PM

All replies

  • did you set applicationtransactionsize?
    Tuesday, June 18, 2013 5:13 AM
  • we are using SqlSyncProvider (over WCF). On the SqlSyncProvider the ApplicationTransactionSize and  MemoryDataCacheSize are both set to 0. During the ProcessChangeBatch() the changes are applied to the database in these 75 rows steps.
    The same happens for the BulkInsertCommand.
    • Edited by MRXS Tuesday, June 18, 2013 7:33 AM
    Tuesday, June 18, 2013 6:54 AM
  • on the wcf service side, are you able to monitor how many files it's actually uploading before applying changes?
    Tuesday, June 18, 2013 9:57 AM
  • batching is disabled, so all changeddata in one DataSet in the DbSyncContext (changeData Parameter). All changes (in the 75 rows steps) are applied during

    this.sqlSyncProvider.ProcessChangeBatch(resolutionPolicy, sourceChanges, changeData, new SyncCallbacks(), syncSessionStatistics);

    I have the impression it's the SqlSyncProvider which is splitting up the BulkUpdate.

    Tuesday, June 18, 2013 11:11 AM
  • afaik, there's nothing there that explicitly splits the application by some arbitrary number
    Tuesday, June 18, 2013 11:28 AM
  • I have removed all WCF code and made a simple table synchronization. I still observe 75 rows update per stored-procedure call. I see the same results in the SyncTrace file as with Sql-Server Profiler. Below the profiler output for one SP-call. This is repeated for every 75 rows.

    SP:Starting    declare @p3 dbo.orders_BulkType
    insert into @p3 values(9675,'2013-06-18 13:06:59.8800000',86,1,82913,1,2002,0)
    insert into @p3 values(9676,'2013-06-18 13:06:59.8830000',86,1,82914,1,2002,0)
    insert into @p3 values(9677,'2013-06-18 13:06:59.8830000',86,1,82915,1,2002,0)
    insert into @p3 values(9678,'2013-06-18 13:06:59.8830000',86,1,82916,1,2002,0)
    insert into @p3 values(9679,'2013-06-18 13:06:59.8830000',86,1,82917,1,2002,0)
    insert into @p3 values(9680,'2013-06-18 13:06:59.8830000',86,1,82918,1,2002,0)
    insert into @p3 values(9681,'2013-06-18 13:06:59.8830000',86,1,82919,1,2002,0)
    insert into @p3 values(9682,'2013-06-18 13:06:59.8870000',86,1,82920,1,2002,0)
    insert into @p3 values(9683,'2013-06-18 13:06:59.8870000',86,1,82921,1,2002,0)
    insert into @p3 values(9684,'2013-06-18 13:06:59.8870000',86,1,82922,1,2002,0)
    insert into @p3 values(9685,'2013-06-18 13:06:59.8870000',86,1,82923,1,2002,0)
    insert into @p3 values(9686,'2013-06-18 13:06:59.8870000',86,1,82924,1,2002,0)
    insert into @p3 values(9687,'2013-06-18 13:06:59.8870000',86,1,82925,1,2002,0)
    insert into @p3 values(9688,'2013-06-18 13:06:59.8900000',86,1,82926,1,2002,0)
    insert into @p3 values(9689,'2013-06-18 13:06:59.8900000',86,1,82927,1,2002,0)
    insert into @p3 values(9690,'2013-06-18 13:06:59.8900000',86,1,82928,1,2002,0)
    insert into @p3 values(9691,'2013-06-18 13:06:59.8900000',86,1,82929,1,2002,0)
    insert into @p3 values(9692,'2013-06-18 13:06:59.8900000',86,1,82930,1,2002,0)
    insert into @p3 values(9693,'2013-06-18 13:06:59.8900000',86,1,82931,1,2002,0)
    insert into @p3 values(9694,'2013-06-18 13:06:59.8900000',86,1,82932,1,2002,0)
    insert into @p3 values(9695,'2013-06-18 13:06:59.8900000',86,1,82933,1,2002,0)
    insert into @p3 values(9696,'2013-06-18 13:06:59.8930000',86,1,82934,1,2002,0)
    insert into @p3 values(9697,'2013-06-18 13:06:59.8930000',86,1,82935,1,2002,0)
    insert into @p3 values(9698,'2013-06-18 13:06:59.8930000',86,1,82936,1,2002,0)
    insert into @p3 values(9699,'2013-06-18 13:06:59.8930000',86,1,82937,1,2002,0)
    insert into @p3 values(9700,'2013-06-18 13:06:59.8930000',86,1,82938,1,2002,0)
    insert into @p3 values(9701,'2013-06-18 13:06:59.8930000',86,1,82939,1,2002,0)
    insert into @p3 values(9702,'2013-06-18 13:06:59.8970000',86,1,82940,1,2002,0)
    insert into @p3 values(9703,'2013-06-18 13:06:59.8970000',86,1,82941,1,2002,0)
    insert into @p3 values(9704,'2013-06-18 13:06:59.8970000',86,1,82942,1,2002,0)
    insert into @p3 values(9705,'2013-06-18 13:06:59.8970000',86,1,82943,1,2002,0)
    insert into @p3 values(9706,'2013-06-18 13:06:59.8970000',86,1,82945,1,2002,0)
    insert into @p3 values(9707,'2013-06-18 13:06:59.8970000',86,1,82946,1,2002,0)
    insert into @p3 values(9708,'2013-06-18 13:06:59.9000000',86,1,82947,1,2002,0)
    insert into @p3 values(9709,'2013-06-18 13:06:59.9000000',86,1,82948,1,2002,0)
    insert into @p3 values(9710,'2013-06-18 13:06:59.9000000',86,1,82949,1,2002,0)
    insert into @p3 values(9711,'2013-06-18 13:06:59.9000000',86,1,82950,1,2002,0)
    insert into @p3 values(9712,'2013-06-18 13:06:59.9000000',86,1,82951,1,2002,0)
    insert into @p3 values(9713,'2013-06-18 13:06:59.9000000',86,1,82952,1,2002,0)
    insert into @p3 values(9714,'2013-06-18 13:06:59.9000000',86,1,82953,1,2002,0)
    insert into @p3 values(9715,'2013-06-18 13:06:59.9000000',86,1,82954,1,2002,0)
    insert into @p3 values(9716,'2013-06-18 13:06:59.9030000',86,1,82955,1,2002,0)
    insert into @p3 values(9717,'2013-06-18 13:06:59.9030000',86,1,82956,1,2002,0)
    insert into @p3 values(9718,'2013-06-18 13:06:59.9030000',86,1,82957,1,2002,0)
    insert into @p3 values(9719,'2013-06-18 13:06:59.9030000',86,1,82958,1,2002,0)
    insert into @p3 values(9720,'2013-06-18 13:06:59.9030000',86,1,82959,1,2002,0)
    insert into @p3 values(9721,'2013-06-18 13:06:59.9030000',86,1,82960,1,2002,0)
    insert into @p3 values(9722,'2013-06-18 13:06:59.9070000',86,1,82961,1,2002,0)
    insert into @p3 values(9723,'2013-06-18 13:06:59.9070000',86,1,82962,1,2002,0)
    insert into @p3 values(9724,'2013-06-18 13:06:59.9070000',86,1,82963,1,2002,0)
    insert into @p3 values(9725,'2013-06-18 13:06:59.9070000',86,1,82964,1,2002,0)
    insert into @p3 values(9726,'2013-06-18 13:06:59.9070000',86,1,82965,1,2002,0)
    insert into @p3 values(9727,'2013-06-18 13:06:59.9070000',86,1,82966,1,2002,0)
    insert into @p3 values(9728,'2013-06-18 13:06:59.9100000',86,1,82967,1,2002,0)
    insert into @p3 values(9729,'2013-06-18 13:06:59.9100000',86,1,82968,1,2002,0)
    insert into @p3 values(9730,'2013-06-18 13:06:59.9130000',86,1,82969,1,2002,0)
    insert into @p3 values(9731,'2013-06-18 13:06:59.9130000',86,1,82970,1,2002,0)
    insert into @p3 values(9732,'2013-06-18 13:06:59.9130000',86,1,82971,1,2002,0)
    insert into @p3 values(9733,'2013-06-18 13:06:59.9130000',86,1,82972,1,2002,0)
    insert into @p3 values(9734,'2013-06-18 13:06:59.9130000',86,1,82973,1,2002,0)
    insert into @p3 values(9735,'2013-06-18 13:06:59.9170000',86,1,82974,1,2002,0)
    insert into @p3 values(9736,'2013-06-18 13:06:59.9170000',86,1,82975,1,2002,0)
    insert into @p3 values(9737,'2013-06-18 13:06:59.9170000',86,1,82976,1,2002,0)
    insert into @p3 values(9738,'2013-06-18 13:06:59.9170000',86,1,82977,1,2002,0)
    insert into @p3 values(9739,'2013-06-18 13:06:59.9170000',86,1,82978,1,2002,0)
    insert into @p3 values(9740,'2013-06-18 13:06:59.9170000',86,1,82979,1,2002,0)
    insert into @p3 values(9741,'2013-06-18 13:06:59.9200000',86,1,82980,1,2002,0)
    insert into @p3 values(9742,'2013-06-18 13:06:59.9200000',86,1,82981,1,2002,0)
    insert into @p3 values(9743,'2013-06-18 13:06:59.9200000',86,1,82982,1,2002,0)
    insert into @p3 values(9744,'2013-06-18 13:06:59.9200000',86,1,82983,1,2002,0)
    insert into @p3 values(9745,'2013-06-18 13:06:59.9200000',86,1,82984,1,2002,0)
    insert into @p3 values(9746,'2013-06-18 13:06:59.9200000',86,1,82985,1,2002,0)
    insert into @p3 values(9747,'2013-06-18 13:06:59.9200000',86,1,82986,1,2002,0)
    insert into @p3 values(9748,'2013-06-18 13:06:59.9200000',86,1,82987,1,2002,0)
    insert into @p3 values(9749,'2013-06-18 13:06:59.9230000',86,1,82988,1,2002,0)

    exec [orders_bulkupdate] @sync_min_timestamp=22080,@sync_scope_local_id=1,@changeTable=@p3   
    SP:StmtStarting    MERGE [orders] AS base USING
    -- join done here against the side table to get the local timestamp for concurrency check
    (SELECT p.*, t.update_scope_local_id, t.scope_update_peer_key, t.local_update_peer_timestamp FROM @changeTable p LEFT JOIN [orders_tracking] t ON p.[order_id] = t.[order_id]) as changes ON changes.[order_id] = base.[order_id]
    WHEN MATCHED AND (changes.update_scope_local_id = @sync_scope_local_id AND changes.scope_update_peer_key = changes.sync_update_peer_key) OR changes.local_update_peer_timestamp <= @sync_min_timestamp THEN
    UPDATE SET [order_date] = changes.[order_date], [domain_id] = changes.[domain_id], [sync_flag] = changes.[sync_flag]
    OUTPUT INSERTED.[order_id] into @changed; -- populates the temp table with successful PKs
     
    SP:Starting    MERGE [orders] AS base USING
    -- join done here against the side table to get the local timestamp for concurrency check
    (SELECT p.*, t.update_scope_local_id, t.scope_update_peer_key, t.local_update_peer_timestamp FROM @changeTable p LEFT JOIN [orders_tracking] t ON p.[order_id] = t.[order_id]) as changes ON changes.[order_id] = base.[order_id]
    WHEN MATCHED AND (changes.update_scope_local_id = @sync_scope_local_id AND changes.scope_update_peer_key = changes.sync_update_peer_key) OR changes.local_update_peer_timestamp <= @sync_min_timestamp THEN
    UPDATE SET [order_date] = changes.[order_date], [domain_id] = changes.[domain_id], [sync_flag] = changes.[sync_flag]
    OUTPUT INSERTED.[order_id] into @changed; -- populates the temp table with successful PKs

    SP:StmtStarting    UPDATE [side] SET [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE(), [sync_flag] = [i].[sync_flag] FROM [orders_tracking] [side] JOIN INSERTED AS [i] ON [side].[order_id] = [i].[order_id]    8    peer1    293576084    orders_update_trigger    VM2008SVR        54    2013-06-18 13:49:12.483    
    SP:Completed    MERGE [orders] AS base USING
    -- join done here against the side table to get the local timestamp for concurrency check
    (SELECT p.*, t.update_scope_local_id, t.scope_update_peer_key, t.local_update_peer_timestamp FROM @changeTable p LEFT JOIN [orders_tracking] t ON p.[order_id] = t.[order_id]) as changes ON changes.[order_id] = base.[order_id]
    WHEN MATCHED AND (changes.update_scope_local_id = @sync_scope_local_id AND changes.scope_update_peer_key = changes.sync_update_peer_key) OR changes.local_update_peer_timestamp <= @sync_min_timestamp THEN
    UPDATE SET [order_date] = changes.[order_date], [domain_id] = changes.[domain_id], [sync_flag] = changes.[sync_flag]
    OUTPUT INSERTED.[order_id] into @changed; -- populates the temp table with successful PKs
      
    SP:StmtStarting    UPDATE side SET
    update_scope_local_id = @sync_scope_local_id,
    scope_update_peer_key = changes.sync_update_peer_key,
    scope_update_peer_timestamp = changes.sync_update_peer_timestamp,
    local_update_peer_key = 0
    FROM
    [orders_tracking] side JOIN
    (SELECT p.[order_id], p.sync_update_peer_timestamp, p.sync_update_peer_key, p.sync_create_peer_key, p.sync_create_peer_timestamp FROM @changed t JOIN @changeTable p ON p.[order_id] = t.[order_id]) as changes ON changes.[order_id] = side.[order_id]
      
    SP:StmtStarting    SELECT [order_id] FROM @changeTable t WHERE NOT EXISTS (SELECT [order_id] from @changed i WHERE t.[order_id] = i.[order_id])
     



    • Edited by MRXS Tuesday, June 18, 2013 11:57 AM
    Tuesday, June 18, 2013 11:55 AM
  • can you do a verbose trace minus the wcf and email me the trace at junetidlethoughts at Hotmail dot com and i'll see what I can dig...

    Tuesday, June 18, 2013 12:35 PM
  • I sended you the verbose trace. I think, because bulk application of changes is used, this implies the table-valued parameter feature is used. The question remains: why is the TVP parameter @changeTable only filled with 75 rows?

    MSDN:

    Bulk Application of Changes

    Sync Framework 2.1 takes advantage of the table-valued parameter feature of SQL Server 2008 and SQL Azure to apply multiple inserts, updates, and deletes by using a single stored procedure call, instead of requiring a stored procedure call to apply each change. This greatly increases performance of these operations and reduces the number of round trips between client and server during change application. Bulk procedures are created by default when a SQL Server 2008 or SQL Azure database is provisioned.

    Wednesday, June 19, 2013 8:29 AM
  • i stand corrected, after going thru the code, it has this value: 0x4b which i is equivalent to 75. 

    it has this unused constant: TVPBatchSize = 0x4b

    but a cursory look at the code, it actually does a check for 0x4b and executes the TVP command.

    why 75, i have no clue :)


    Wednesday, June 19, 2013 9:24 AM
  • I have nothing against 75, but it's a bit curious when there is the possibility of bulk updates, this is only done for just 75 rows. Like you saw in my trace for 10000 rows how often the sp is called. At the moment we have performance issues during ProcessChangeBatch (->bulk update and insert), and in my opinion this is one of the possible performance killer. At least the TVPBatchSize should be externally configurable.
    Wednesday, June 19, 2013 9:47 AM
  • Did you ever find out why?  Can someone from the Microsoft Sync team respond? 

    We also want to increase this batching figure and would like to know why the team picked the number 75.


    • Edited by stupy Tuesday, October 01, 2013 4:15 PM
    Tuesday, October 01, 2013 4:15 PM