locked
Microsoft Sync framework slow on tables with > 30000 records RRS feed

  • Question

  • Hi,

    We have few clients running on SQL Server Compact database syncing with SQL Server 2008 Express server database using Sync Framework v3.5 for a about a year now. It seems that for tables with large number of records (i.e. > 20000), it roughly takes more than a minute to sync and CPU reaches 100% as well.

    This only happens to some of the clients hence, not a performance issue with the server. I'm puzzled as to why this could happen. Any help is much appreciated.


    Kind regards,

    Sasanka.

    Wednesday, October 5, 2011 4:19 AM

Answers

  • see: http://msdn.microsoft.com/en-us/library/cc807160(SQL.110).aspx

    it's also in the docs installed with the SDK

    Thursday, October 6, 2011 1:47 AM
  • Ok, I managed to find a solution to this :). It appears that this is an issue in Sync Framework 2.0 or lower. The solution would be to upgrade to sync framework 2.1 and recreate the client database.

    Issue

    The issue here is that every time we execute the sync process, three records are appended to __syncTransactions table (I believe one per each table group). Over time this table grows into 1000s of records. When the sync framework tries to look for inserts in a particular table, it cross references the value in __syncTransactions with __sysInsertTxBsn column (I've attached this query in my earlier post) and for tables with large number of data the query becomes terribly slow.

     It appears that this issue happens on databases created by Sync Framework 2.0 or lower version.

     How does Sync Framework 2.1 Fix the Issue?

     It appears that if I get Sync Framework 2.1 to create my client database and sync with the server, rather than appending three records to __syncTransactions, it removes existing records and adds three. This will prevent __syncTransaction table from growing into 1000s of records over time and will only contain 3 records (I have three table groups) at any given time. 

    However, when I tried using an existing database created by Sync Framework 2.0 and used 2.1 to sync with the server, it appended 3 records to __syncTransactions table rather than truncate and adding 3. Therefore, the only way to solve this problem would be to upgrade sync framework to 2.1 and recreate client databases.

    Hope this helps :).

    Wednesday, October 12, 2011 11:25 PM

All replies

  • which provider are you using? have you tried enabling sync framework tracing to find out where it's spending most time?
    Wednesday, October 5, 2011 5:03 AM
  • Do you havea link that explains the setup of tracing? 

     

    Thanks

    JJ

    Wednesday, October 5, 2011 5:43 PM
  • see: http://msdn.microsoft.com/en-us/library/cc807160(SQL.110).aspx

    it's also in the docs installed with the SDK

    Thursday, October 6, 2011 1:47 AM
  • Hi JuneT,

    Thanks heaps for your reply. I'm using DbServerSyncProvider & SqlCeClientSyncProvider provider. I haven't tried enabling the trace yet but will try that and see what I can find and inform my findings.

    It is strange that once I delete the client database and get the sync process to recreate and download changes from the server, everything goes back to normal. 

    Cheers,

    Sasanka.

    Friday, October 7, 2011 4:50 AM
  • Hi JuneT,

    Thanks heaps for your help on setting up the trace on my sync application. Based on the sync trace logging, I managed to narrow down the query that was behind the slow syncing. 

    Following is the query that looks for inserts in one of the tables that contains ~70,000 records:

    I have highlighted line that takes roughly 1 minute to execute. The reason is that __syncTransaction table contains around 1200 records and due to the cross reference with 70,000 odd records in my tblPermissionGroupResourceRole table, the query is quite slow.

    My question is, what is the function of __syncTransaction table? Is it possible for me to either manually/automatically clear this table out? 

    Thanks again for pointing towards the right path and as always your help is much appreciated. 

    Kind regards,

    Sasanka.

    Monday, October 10, 2011 6:57 AM
  • that's the built-in Sql Compact Change Tracking and you can't just clean it up as Sync Framework uses that information to determine what has changed.

     

    Monday, October 10, 2011 8:27 AM
  • Hi JuneT,

    Any suggestions as to how I can resolve this? There are built in methods available to clean metadata but unfortunately they are table based and does not clear data in __syncTransactions table. 

    Kind regards,

    Sasanka.

    Tuesday, October 11, 2011 3:12 AM
  • which clean metadata methods are you referring to?
    Tuesday, October 11, 2011 3:42 AM
  • There is a call called SqlCeSyncStoreMetadataCleanup which clears metadata of a data row older than a certain number of days. I believe this is a feature in Sync Framework 2.1.

    http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.sqlserverce.sqlcesyncstoremetadatacleanup.aspx

    Wednesday, October 12, 2011 4:02 AM
  • i doubt that you can use the SqlCeSyncStoreMetadataCleanup  as i think it's for the cleaning up database provisioned using the SqlCeSyncScopeProvisioning API that works together with SqlCeSyncProvider whereas the one your using is the SqlCeClientSyncProvider.
    Wednesday, October 12, 2011 12:06 PM
  • Ok, I managed to find a solution to this :). It appears that this is an issue in Sync Framework 2.0 or lower. The solution would be to upgrade to sync framework 2.1 and recreate the client database.

    Issue

    The issue here is that every time we execute the sync process, three records are appended to __syncTransactions table (I believe one per each table group). Over time this table grows into 1000s of records. When the sync framework tries to look for inserts in a particular table, it cross references the value in __syncTransactions with __sysInsertTxBsn column (I've attached this query in my earlier post) and for tables with large number of data the query becomes terribly slow.

     It appears that this issue happens on databases created by Sync Framework 2.0 or lower version.

     How does Sync Framework 2.1 Fix the Issue?

     It appears that if I get Sync Framework 2.1 to create my client database and sync with the server, rather than appending three records to __syncTransactions, it removes existing records and adds three. This will prevent __syncTransaction table from growing into 1000s of records over time and will only contain 3 records (I have three table groups) at any given time. 

    However, when I tried using an existing database created by Sync Framework 2.0 and used 2.1 to sync with the server, it appended 3 records to __syncTransactions table rather than truncate and adding 3. Therefore, the only way to solve this problem would be to upgrade sync framework to 2.1 and recreate client databases.

    Hope this helps :).

    Wednesday, October 12, 2011 11:25 PM