none
Slow Response of Sql Server CE Change Enumeration within Sync Framework RRS feed

  • General discussion

  • I have a sync configured between SQL Server Express and SQL Server CE, and it sync all the records.  But it seems to be very slow to upload the same amount of data from CE compared to the same # rows downloaded from Express.

    I reviewed the process using the SyncTracing methods and what I found was that the SQL Server CE Change Enumeration was running through all the data rows 100K+. 

    For a small amount of change data (5 inserts, 5 deletes & 5 updates) it was taking almost 26 minutes, 25 of which were spent enumerating the 100K+ rows of data that were already sync'd and unchanged.

    Is there some configuration changes that would be handle change tracking in CE, or a better Sync Provider for CE than was comes as part of the framework?

    Thanks.

    Wednesday, March 24, 2010 8:44 PM

All replies

  • Hi Timothy,

    Does it consistently do this? Meaning it always go thru all rows on CE everytime you sync?

    if you're seeing it enumerating all rows from CE, do you see it applying it on the server and getting conflicts?

     

     

    Thursday, March 25, 2010 11:52 PM
    Moderator
  • Yes, I have run the sync a number of times with the tracing on and each time I have local (CE) changes to upload to the remote server, it gives the following two lines for each unchanged previously synced record.

     

    VERBOSE, SyncTestKit.vshost, 11, 03/23/2010 16:47:42:257,       Change ignored for row with PK: UID = c174d547-15be-4c6a-a6e8-fe750468acb1 on C:\Documents and Settings\danielti\My Documents\Projects-Local\TestData\UserTest\UserSync_local.SDF

    VERBOSE, SyncTestKit.vshost, 11, 03/23/2010 16:47:42:267,          Mapped knowledge already contains UpdateVersion: (1,495456)

    So 100K records we unchanged, I then added 10, updated 10 different records, and deleted 10 different records.

    The end of the local enumeration shows

    INFO   , SyncTestKit.vshost, 11, 03/23/2010 16:47:48:301,       Inserts: 10
    INFO   , SyncTestKit.vshost, 11, 03/23/2010 16:47:48:301,       Deletes: 10
    INFO   , SyncTestKit.vshost, 11, 03/23/2010 16:47:48:311,       Updates: 10
    INFO   , SyncTestKit.vshost, 11, 03/23/2010 16:47:48:311,       Changes Enumerated: 30

     

    Because this is all test data, I could send you the last trace.  I should have probably already stated this but we are using Offline Replication.

     

    Thanks.

    Friday, March 26, 2010 2:18 PM
  • looks like the sync_min_timestamp being passed is so low it retrieves most rows.

    But the behaviour you're getting is just right, the provider selects rows from the client retrieving all rows > than the sync_min_timestamp. It then goes thru to each row to check if the destination knowledge already contains that particular version of the row, if it doesnt, then its added in the change batch, if it is, its ignored.

    as to how the sync_min_timestamp get set, the MS guys can probably shed more light :)

    just curious, did the 100K rows initially came from the server or were they existing rows in the SDF that was sync up to the server?

    Saturday, March 27, 2010 7:23 AM
    Moderator
  • Okay, I have been poking around the SDF tables using SSMS 2008 and tracing back how the sync_min_timestamp is set. 

    With our testing we inserted the rows into the SQL Server and then created the sdf file and provisioned it and then sync'd it.  That gives us a starting point.  We then backed up the database and SDF.  For every test cycle we restore the backups.

     

     

    Monday, March 29, 2010 6:20 PM
  • So I found out why it is seemingly enumerating every record in the database, The are times when the data is in sync between and the meta data is not fully.  Let me give and example gleaned from the SyncTracer.  I was using a SQL Express DB as my remote and SQL CE and my local data store.

    Please note that this is for Download then Upload, if the user selects Upload then download the remote and local noted below would be reversed.

    1.  Insert 5 new rows into Express (remote).

    2.  Insert 10 new rows into CE (local).

    3.  Run Sync

         3a.  enumerates 5 inserts in Express (remote)

         3b.  inserts 5 rows into CE (local)

         3c.  enumerates 10 inserts in CE (local) and 5 existing (non-action) changes from step 3b.

         3d.  inserts 10 rows in Express (remote)

     

    If I immediately try again (download then upload), here is what the trace shows.

    1.  Run Sync

         1a.  enumerates 10 existing (non-action) changes in Express (remote) from 3d above.

         1b.  no changes applied into CE (local)

         1c.  no enumerations found in CE (local)

         1d.  no changes applied into Express (remote).

     

    Now if I run the Sync a third time, the are now "changes" found and nothing enumerated on either side.  So what I was seeing was a function of the procedure that I was using to set the initial state of the testing.  Also, I am going to be doing some additional test doing the sync's one direction at a time.

     

    Thanks for your questions, they pushed me in the right direction.

    Thursday, April 1, 2010 3:06 PM