Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
sync performance varies from computer to computer and can be very slow RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I am sync'ing 2 SQL Compact databases with a central SQL Server 2008. I have approx 20,000 rows in 1 table. The computer that initiatially populated the table with the rows syncs very quickly. The computer that initially got the data by downloading the rows by sync'ing with SQL Server 2008 takes about 60 seconds to sync every time sync is called, even when there are no changes in any of the databases.

    I am using the SQL Server sync framework providers - both for SQL Compact and SQL Server, and am syncing over WCF. I've noticed that on the WCF server, during the course of the sync from the slow computer, CPU is very high. Whereas when sync'ing from the quick computer it is minimal.

    Also, on the slow computer the SQL Compact database is double the size of the SQL Compact database on the quick computer,

    Thursday, June 17, 2010 7:04 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    It is related to foreign key constraints, and SQL Compact Edition 3.5. SP2 resolved the problem. Thanks for everyone's comments - they pointed me in the right direction.
    • Marked as answer by JuneT Wednesday, July 21, 2010 4:12 AM
    Wednesday, July 21, 2010 3:38 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    What types of sync providers are you using for Compact db and Sql server? Are they SqlCeClientSyncProvider and DBServerSyncProvider, or SqlCeSyncProvider and SqlSyncProvider?

    Also, do these two machines have same machine config and same sync application? Do the two compact databases have the same schema and same data?

     

     

    Thursday, June 17, 2010 5:42 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    I'm using SqlCeSyncProvider and SqlSyncProvider. Both client machines have the same config. Both sets of SQL Compact Database 3.5 databases are generated by sync'ing from the same SQL Server 2008, so they both have identical schema and data.

    If I delete the problem (client) database on the problem machine and rebuild the SQL Compact database using sync the problem goes away. However, if I retain the problem database even after a reboot of all machines the problem persists. So it appears there is something about the state of the SQL Compact 3.5 database that underlies the problem.

    Some further detail on the behavior of the WCF server. In the case where performance is very bad, the server repeatedly makes calls to GetChanges followed by DownloadBatchFile (even though no changes have been made to any database table). Each of the batch files downloaded is more than 100KB in size.

    I have 2 example SQL Compact databases, both of which should be identical because they were both sync'ed off the same server. One performs very slowly. The other is fine. I can provide these if necessary. They're each about 10MB in size.

    Thanks, Michael

    Friday, June 18, 2010 1:34 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    >>> If I delete the problem (client) database on the problem machine and rebuild the SQL Compact database using sync the problem goes away. However, if I retain the problem database even after a reboot of all machines the problem persists. So it appears there is something about the state of the SQL Compact 3.5 database that underlies the problem.

    so after rebuilding the client db ont he "problem" machine by syncing to the SqlServer fixed the problem ? or the problem stays with the subsequent sync ? seems to be the probem gone away, but could you please confirm ?

    could this be repro-able ? I would be very curious to know what is the cause of it. the database is not likely to be in a bad state if you can still access it. however there would be some metadata issue due to some reason which we dont know yet that would be cauing this behavior.

    thanks

    Yunwen

     

    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by AWong5 Friday, March 25, 2011 3:09 PM
    • Unproposed as answer by AWong5 Friday, March 25, 2011 3:09 PM
    Friday, June 18, 2010 6:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    rebuilding the client database by sync'ing with SqlServer definitely fixes the problem. The way I have been able to generate the problem is by running 3 or 4 clients consurrently periodically syn'ing data up to SQL Server running over a few hours, each using its own scope. Each client posts 40 records to SQL Compact, syncs, pauses for 30 seconds, and then posts another 40 records. I shall see if I can come up with a demo that easily reproduces the problem. Are you interested in copies of good and bad SQL Compact databases? (each about 10MB)?
    Friday, June 18, 2010 11:25 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    ArcSend, Thank you for the investigation! it would be great if you can have a demo which can reproduce this problem.

    During your investigation, you could also turn on tracing and if possible compare the bad db sync session with the good db sync session and see whether there are suspected things happening in the the bad db sync session. The tracing instruction is at http://msdn.microsoft.com/en-us/library/cc807160.aspx. Basically copy the xml config to yourProgram.exe.config.

    Saturday, June 19, 2010 12:06 AM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    I've added tracing and generated 2 trace outputs: one where where sync runs normally and one where sync runs very slowly. To summarise:

    i) I run a load test where I have 4 clients using 4 different scopes posting data via sync from SQL Compact 3.5 to SQL Server. I run these clients for around 4 hours

    ii) I have a separate client on a different machine, and sync periodically with one of the four scopes mentioned in i)

    iii) This client behaves well for a 2 to 3 hours, but then sync becomes dramatically slower (from a matter of 4 or 5 seconds to 2 minutes). Even after all database updating has stopped, this client's sync continues to run just as slowly, and can only be fixed by rebuilding the client SQL Compact database.

    iv) If I rebuild the SQL Compact database on the bad client (via sync) everything returns to normal.

    v) The principal difference between the 2 trace files is that for the bad sync there are literally thousands of entries of the following:

    INFO   , sibi.ASA, 1, 06/29/2010 04:35:55:273,          Item excluded from sync, will retry next sync
    INFO   , sibi.ASA, 1, 06/29/2010 04:35:55:274,          Not a data conflict, retry next sync
    INFO   , sibi.ASA, 1, 06/29/2010 04:35:55:274,          Item excluded from sync, will retry next sync
    INFO   , sibi.ASA, 1, 06/29/2010 04:35:55:274,          Not a data conflict, retry next sync
    INFO   , sibi.ASA, 1, 06/29/2010 04:35:55:274,          Item excluded from sync, will retry next sync
    INFO   , sibi.ASA, 1, 06/29/2010 04:35:55:275,          Not a data conflict, retry next sync

    What does this mesage indicate, and why is it being generated? Where sync is running normally not a single line such as the above is produced.

    Tuesday, June 29, 2010 5:39 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    interesting, i think this error get's raised when an error occurs applying change, normally a SQL error.
    Thursday, July 8, 2010 1:26 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Looks like the change relates to this "No key matching the described characteristics could be found within the current range." - which is a known issue with SQL Compact Edition 3.5. Am hoping SP2 will fix it.
    • Proposed as answer by Jesse L - MSFT Thursday, July 15, 2010 11:00 PM
    Tuesday, July 13, 2010 5:42 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    The Not a data conflict, retry next sync could also be related to any constraint errors. 
    Thursday, July 15, 2010 10:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    It is related to foreign key constraints, and SQL Compact Edition 3.5. SP2 resolved the problem. Thanks for everyone's comments - they pointed me in the right direction.
    • Marked as answer by JuneT Wednesday, July 21, 2010 4:12 AM
    Wednesday, July 21, 2010 3:38 AM