none
Sync Services for ADO.net Performing Un-Needed Updates on the Client

    Question

  • I think I have a problem with how I have set up my sync project.  I am syncing the data fine now, but the sync seems to be performing un-needed updates.  The best example I have is that after the first initial sync, the trace log shows that the sync performed updates on all of the rows that it just inserted.  Another example is that after I had synced the data for the first time and then several hours later performed another sync again it re-updated all of the rows again.  This is very slow and is not working correctly because the database would have had very few if any changes to the data to justify what it's doing.  I am using batching in the sync.  I'm using sync services for ado.net (devices).  Please let me know any suggestions you have.  Thanks for any help.

    CEDeveloper

    Sunday, June 23, 2013 4:57 PM

Answers

  • i suggest you check your batching.

    also, is the initial 10435 rows done on first sync? i mean it's the first time its doing a sync?

    • Marked as answer by CEDeveloper Wednesday, July 03, 2013 6:08 PM
    Tuesday, June 25, 2013 5:43 AM
    Moderator

All replies

  • run SQL Profile to capture the selectincrementalXXX commands, and see whether your SQL is returning rows even if theyre not changed.
    Monday, June 24, 2013 1:02 AM
    Moderator
  • Hi JuneT.  I ran SQL Profiler and this is what it shows.  I'm only showing one table that's being synced.  When I start the program I'm syncing the database client with 3 tables on the server.  The STATES table is one of those 3.  I did notice in the SQL Profiler that the top 2 commands below are executed for every table that I have available to sync (around 25) every time between each of the sets of commands involved in the specific table.  That doesn't make any sense to me that that step would be done every time.  Anyway, I'm sure something is not set up right.  Looking at the 2 bottom commands that look they are getting the created rows need to sync and the updated rows needed to sync, it would definitely insert all records and then go back and update all records.  This is the initial sync, did I set up something wrong?  Thanks for all your help.

     SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT * FROM [STATES] SET FMTONLY OFF; SET NO_BROWSETABLE OFF;
    
     SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT * FROM [STATES] SET FMTONLY OFF; SET NO_BROWSETABLE OFF;
    
    exec sp_reset_connection 
    
    exec sp_executesql N' SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT [STATE] FROM [STATES] WHERE (CREATE_DATETIME > @sync_last_received_anchor AND CREATE_DATETIME <= @sync_new_received_anchor AND CREATE_ID <> @sync_originator_id)',N'@sync_last_received_anchor binary(8000),@sync_new_received_anchor binary(8000),@sync_originator_id int',@sync_last_received_anchor=NULL,@sync_new_received_anchor=NULL,@sync_originator_id=NULL
    
    exec sp_executesql N' SET FMTONLY OFF; SET NO_BROWSETABLE ON;SELECT [STATE] FROM [STATES] WHERE (CREATE_DATETIME > @sync_last_received_anchor AND CREATE_DATETIME <= @sync_new_received_anchor AND CREATE_ID <> @sync_originator_id)',N'@sync_last_received_anchor binary(8000),@sync_new_received_anchor binary(8000),@sync_originator_id int',@sync_last_received_anchor=NULL,@sync_new_received_anchor=NULL,@sync_originator_id=NULL
    
    
    declare @p4 int  set @p4=825117062  exec sp_executesql N'SELECT @sync_originator_id = @sync_client_id_hash',N'@sync_client_id_hash int,@sync_originator_id int output',@sync_client_id_hash=825117062,@sync_originator_id=@p4 output  select @p4
    
    declare @p2 binary(8)  set @p2=0x0000000001C91B02  declare @p3 binary(8)  set @p3=0x0000000001C91B02  declare @p5 int  set @p5=1 exec usp_GetNewBatchAnchorNewWTable @sync_last_received_anchor=0x0000000000000000,@sync_max_received_anchor=@p2 output,@sync_new_received_anchor=@p3 output,@sync_batch_size=100,@sync_batch_count=@p5 output,@sync_table_name='STATES'  select @p2, @p3, @p5
    
    exec sp_executesql N'SELECT [STATE] FROM [STATES] WHERE (CREATE_DATETIME > @sync_last_received_anchor AND CREATE_DATETIME <= @sync_new_received_anchor AND CREATE_ID <> @sync_originator_id)',N'@sync_last_received_anchor binary(8000),@sync_new_received_anchor binary(8),@sync_originator_id int',@sync_last_received_anchor=0x00,@sync_new_received_anchor=0x0000000001C91B02,@sync_originator_id=825117062
    
    exec sp_executesql N'SELECT [STATE] FROM [STATES] WHERE (UPDATE_DATETIME > @sync_last_received_anchor AND UPDATE_DATETIME <= @sync_new_received_anchor AND UPDATE_ID <> @sync_originator_id AND  NOT (CREATE_DATETIME > @sync_last_received_anchor AND CREATE_ID <> @sync_originator_id))',N'@sync_last_received_anchor timestamp,@sync_new_received_anchor timestamp,@sync_originator_id int',@sync_last_received_anchor=0x0000000000000000,@sync_new_received_anchor=0x0000000001C91B02,@sync_originator_id=825117062


    CEDeveloper

    Tuesday, June 25, 2013 12:22 AM
  • if you look at the commands, there are separate commands for selecting inserts, updates and deletes (selectincrementalinserts, selectincrementalupdates, selectincrementaldeletes)


    Tuesday, June 25, 2013 1:32 AM
    Moderator
  • Sorry, I think I just shot myself in the foot by posting that table.  Looking back at the server logs that one did not have updates that occurrred after the initial insert.  This is one of the examples of what I saw in the server trace that concerned me.  It looks like it updated all of the records again after inserting them.  As you can see below it updated this table hundreds of times after it did the inserts, but the thing is that no data would have changed between the time it did the inserts.  I'll get the sql profiler information for when this table is synced and post that.

    INFO   , w3wp, 10, 06/23/2013 05:24:41:314,       Changes Enumerated: 99
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="23522"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="23525"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="23526"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="25409"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="23527"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="23529"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="23537"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="23538"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="23966"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="23539"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="23965"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4322"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4330"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4331"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4335"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4336"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4338"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4339"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4340"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="24690"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4341"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4342"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4343"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4344"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4345"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4346"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4348"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4349"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4350"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="24689"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4351"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4352"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4358"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4359"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4360"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4363"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4364"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4365"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4366"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4367"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4368"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4369"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4370"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4371"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4373"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4374"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4375"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4381"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4382"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="24159"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4383"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4384"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4385"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4393"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4394"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4395"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4400"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4401"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4402"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4404"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4405"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4406"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4407"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4408"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4409"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4410"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4411"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4412"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4413"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4414"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4415"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4416"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4417"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4418"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4425"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="24808"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4426"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="24577"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4435"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="24566"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="24586"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4437"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4438"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4439"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4440"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4442"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="23663"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4444"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4445"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4450"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="25726"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="25725"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4451"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4452"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4453"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4455"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4456"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:314,       Update for row with PK: CIDENTITY="4457"
    VERBOSE, w3wp, 10, 06/23/2013 05:24:41:330,       Update for row with PK: CIDENTITY="4458"


    CEDeveloper

    Tuesday, June 25, 2013 2:27 AM
  • Ok. I did the sync and captured the activity with SQL Profiler.  Looking at the server trace log the sync inserted all of the rows in one batch (10435 rows) and then went through and updated all of the rows in batches of 100 at a time which is what it should have done for the inserts and shouldn't have done the updates.  This is a biderectional sync table.  SQL profiler logged that the sync services executed 18969 commands to process the syncronization for that one table.  Something doesn't seem to be working right.  Is there something specific that I can post from either the SQL Profiler log or the Server trace that may help determine what's wrong?  Thanks for your help.


    CEDeveloper


    • Edited by CEDeveloper Tuesday, June 25, 2013 3:23 AM forgot something
    Tuesday, June 25, 2013 3:16 AM
  • i suggest you check your batching.

    also, is the initial 10435 rows done on first sync? i mean it's the first time its doing a sync?

    • Marked as answer by CEDeveloper Wednesday, July 03, 2013 6:08 PM
    Tuesday, June 25, 2013 5:43 AM
    Moderator
  • Thanks JuneT.  Yes, the 10435 rows were on the first sync.  I did find out what the problem was.  Since I created custom tracking for the database and the database already had rows in it, the create datetime field was set a binary(8) with default value of @@dbts+1.  When it created the column, all of the rows got the same value for the create_datetime field.  I went in and updated the create_datetime value to be the same as the update_datetime value and it seemed to work for this table.  The batching worked correctly after that doing 100 inserts each time and no updates.  Even though this is correct now.  The sync process is executing the following 2 statements to the database between each new anchor command value and it calls it for every single table that could be synced not just the ones that are currently being synced.  For the synchronization of this one table the below statements got called for each table that's defined in the WCF service for a total of over 12,000 times to the database.  What could be causing it do that?  Thanks for all your help.

    SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT * FROM [STATES] SET FMTONLY OFF; SET NO_BROWSETABLE OFF;

    SET FMTONLY OFF
    ; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT * FROM [STATES] SET FMTONLY OFF; SET NO_BROWSETABLE OFF;


    CEDeveloper

    Tuesday, June 25, 2013 2:30 PM