locked
very slow performance with Sync Framework on a device RRS feed

  • Question

  • Hi

    I'm developing a scanner application for an MC3090 scanner (Windows CE 5.0, with SQL Server Compact 3.5). I develop on Windows XP x86, with VS2008.  I'm using the Sync Framework to sync some tables (in this case only to download to the scanner but I'll need upload as well). I set up a web service and run it in IIS.

    When I tested the sync (two tables with around 10 columns, one table with 5000 rows and the other with 12000 rows) this took more than 800 seconds wireless! I tried again using the usb connection and it still took 692 seconds.

     

    This is what I do serverside in my constructor:

            m_serverProviderPlu = New DbServerSyncProvider()
            Dim builder As New SqlConnectionStringBuilder()

            builder.DataSource = "dbdev"
            builder.IntegratedSecurity = False
            builder.InitialCatalog = "dbMEATart"

            builder.UserID = "testjoachim"
            builder.Password = "Friday13th!"
            Dim serverConnection As New SqlConnection(builder.ConnectionString)
            m_serverProviderPlu.Connection = serverConnection

            Dim pluSyncAdapterBuilder As New SqlSyncAdapterBuilder
            pluSyncAdapterBuilder.Connection = serverConnection
            'pluSyncAdapterBuilder.SyncDirection = SyncDirection.Snapshot 'mss moet dit hier niet

            'tabel zelf
            pluSyncAdapterBuilder.TableName = "tblPlu"
            pluSyncAdapterBuilder.DataColumns.Add("PLU")
            pluSyncAdapterBuilder.DataColumns.Add("BasisPLU")
            pluSyncAdapterBuilder.DataColumns.Add("VerkoopArtikelID")
            pluSyncAdapterBuilder.DataColumns.Add("ArtikelID")
            pluSyncAdapterBuilder.DataColumns.Add("Actief")
            pluSyncAdapterBuilder.DataColumns.Add("PrijsEhID")
            pluSyncAdapterBuilder.DataColumns.Add("BestelEhID")
            pluSyncAdapterBuilder.DataColumns.Add("BTWCode")
            pluSyncAdapterBuilder.DataColumns.Add("ActivatieDatum")

            pluSyncAdapterBuilder.DataColumns.Add("DatumCreatie")
            pluSyncAdapterBuilder.DataColumns.Add("DatumWijziging")
            pluSyncAdapterBuilder.DataColumns.Add("GebruikerCreatie")
            pluSyncAdapterBuilder.DataColumns.Add("GebruikerWijziging")
            'rest voorlopig niet

            'afwerking
            ' pluSyncAdapterBuilder.CreationOriginatorIdColumn = "GebruikerCreatie"
            pluSyncAdapterBuilder.CreationTrackingColumn = "DatumCreatie"
            ' pluSyncAdapterBuilder.UpdateOriginatorIdColumn = "GebruikerWijziging"
            pluSyncAdapterBuilder.UpdateTrackingColumn = "DatumWijziging"

            Dim ad = pluSyncAdapterBuilder.ToSyncAdapter
            DirectCast(ad.SelectIncrementalInsertsCommand.Parameters("@sync_last_received_anchor"), SqlParameter).DbType = DbType.DateTime
            'DirectCast(ad.SelectIncrementalUpdatesCommand.Parameters("@sync_last_received_anchor"), SqlParameter).DbType = DbType.DateTime
            m_serverProviderPlu.SyncAdapters.Add(ad)

            Dim artikelSyncAdapterBuilder As New SqlSyncAdapterBuilder
            artikelSyncAdapterBuilder.TableName = "tblArtikel"
            artikelSyncAdapterBuilder.DataColumns.Add("ArtikelID")
            artikelSyncAdapterBuilder.DataColumns.Add("Omschrijving")
            artikelSyncAdapterBuilder.DataColumns.Add("HoofdgroepID")
            artikelSyncAdapterBuilder.DataColumns.Add("SubgroepID")
            artikelSyncAdapterBuilder.DataColumns.Add("Diepvries")
            artikelSyncAdapterBuilder.DataColumns.Add("DatumCreatie")
            artikelSyncAdapterBuilder.DataColumns.Add("DatumWijziging")
            artikelSyncAdapterBuilder.DataColumns.Add("GebruikerCreatie")
            artikelSyncAdapterBuilder.DataColumns.Add("GebruikerWijziging")

            artikelSyncAdapterBuilder.CreationTrackingColumn = "DatumCreatie"
            artikelSyncAdapterBuilder.UpdateTrackingColumn = "DatumWijziging"

            artikelSyncAdapterBuilder.Connection = serverConnection

            m_serverProviderPlu.SyncAdapters.Add(artikelSyncAdapterBuilder.ToSyncAdapter)


            ' select new anchor command
            Dim anchorCmd As New SqlCommand()
            anchorCmd.CommandType = CommandType.Text
            anchorCmd.CommandText = "SELECT @sync_new_received_anchor = getdate()"
            anchorCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.DateTime).Direction = ParameterDirection.Output

            m_serverProviderPlu.SelectNewAnchorCommand = anchorCmd

    This is what I do on the device:

                m_syncAgentPLU = New SyncAgent()
                m_servicePLU = New PluSyncWebService.Service
                m_syncAgentPLU.RemoteProvider = New ServerSyncProviderProxy(m_servicePLU)

                Dim fileLoc = "\Application\db\dbPlu.sdf"

                Dim connString As String = "Data Source=" + fileLoc + "; Default Lock Timeout = 10000"

                If Not File.Exists(fileLoc) Then
                    Dim clientEngine As New SqlCeEngine(connString)
                    clientEngine.CreateDatabase()
                    clientEngine.Dispose()
                End If

                Dim clientSyncProvider As New SqlCeClientSyncProvider(connString)

                m_syncAgentPLU.LocalProvider = clientSyncProvider

                Dim tblPlu As New SyncTable("tblPlu")
                tblPlu.CreationOption = TableCreationOption.DropExistingOrCreateNewTable  'MOET NOG ANDERS!
                tblPlu.SyncDirection = Data.SyncDirection.DownloadOnly

                Dim tblArtikel As New SyncTable("tblArtikel")
                tblArtikel.CreationOption = TableCreationOption.DropExistingOrCreateNewTable  'MOET NOG ANDERS!
                tblArtikel.SyncDirection = Data.SyncDirection.DownloadOnly

                Dim pluGroup As New SyncGroup("PLU")
                tblPlu.SyncGroup = pluGroup
                tblArtikel.SyncGroup = pluGroup

                m_syncAgentPLU.Configuration.SyncTables.Add(tblArtikel)
                m_syncAgentPLU.Configuration.SyncTables.Add(tblPlu)

    I tried installing a hotfix from the microsoft site, but this didn't have any effect.

     

    Any ideas? I'm struggling with the sync framework for several weeks now and it's really getting on my nerves.

    Thanks in advance, Joachim

    Tuesday, July 26, 2011 2:16 PM

All replies

  • have you tried enabling Sync Framework tracing to at least give you a clue where its taking more time? Does your database server and mobile client have the same language settings/collation?

    also, you may want to read this as well:

    Synchronization Services for ADO .NET for Devices: Improving performance by skipping tables that don’t need synchronization



    Wednesday, July 27, 2011 1:10 AM
  • Thanks for the answer

    I tried to use a trace configuration file (http://msdn.microsoft.com/en-us/library/cc807160.aspx), but got the same problems as described in this document: http://social.microsoft.com/Forums/en-US/syncgeneral/thread/d27670dc-74d2-4e0c-bdd1-315a5e7a75af

     

    On the MSDN article, it seems like the only thing you need is te configuration file, but it doesn't do anything for me.

     

     

     



    Wednesday, July 27, 2011 8:57 AM
  • I've done some logging (with a table which takes about 2 minutes to sync (downloadonly). I used the SessionProgress event to log each progress. In the meanwhile, I ran a .NET CF network log, which indicated that the downloading of the records only took 7 seconds (I even shut down the WCF service after those 7 seconds, and the sync still succeeds):

     

    Stage: ReadingMetadata ; Tijdstip: 8/10/2011 1:10:36 PM
    Stage: CreatingSchema ; Tijdstip: 8/10/2011 1:10:36 PM
    Stage: UploadingChanges ; Group: Scannings ; Tijdstip: 8/10/2011 1:10:51 PM
    Stage: UploadingChanges ; Group: Scannings ; Tijdstip: 8/10/2011 1:10:53 PM
    Stage: DownloadingChanges ; Group: tblPlu ; Tijdstip: 8/10/2011 1:10:53 PM

    last network activitty = 1:11:00! (endRecv() message which stored the last records of the table in XML format)

    from here, everything is downloaded to the scanner!!

    Stage: DownloadingChanges ; Group: tblPlu ; Tijdstip: 8/10/2011 1:13:20 PM
    Stage: WritingMetadata ; Tijdstip: 8/10/2011 1:13:20 PM

     

    This is very strange to me because I really thought the problem was network related. Now I think it's the SQL Compact itself.

     


    Wednesday, August 10, 2011 2:03 PM
  • Uploading seems to have the opposite problem. The rows are commited to the database but it still takes +- 50 seconds from then till the actual synchronisation is completed:

     

    Stage: ReadingMetadata ; Tijdstip: 8/11/2011 3:08:14 PM

    Stage: UploadingChanges ; Group: Scannings ; Tijdstip: 8/11/2011 3:08:15 PM

     

    3:09:51 first row upload

    3:10:07 last row upload (5000 rijen)

     I did a select around here and all rows were commited to the database.

     

    Stage: UploadingChanges ; Group: Scannings ; Tijdstip: 8/11/2011 3:10:49 PM

    Stage: DownloadingChanges ; Group: tblPlu ; Tijdstip: 8/11/2011 3:10:49 PM

     

    3:10:50 last network activity

     

     

    Stage: DownloadingChanges ; Group: tblPlu ; Tijdstip: 8/11/2011 3:10:51 PM

    Stage: WritingMetadata ; Tijdstip: 8/11/2011 3:10:51 PM

    Thursday, August 11, 2011 1:41 PM
  • after the ApplyChanges on the server side, the return value of the ApplyChanges is a SyncContext that contains the original dataset containing the changes, a confict dataset and some other metadata for sync statistics. These datasets needs to be serialized over the wire.when the sync is done, the client has to update its anchor table as well for the lastreceivedanchor and lastsentanchor.

    Thursday, August 11, 2011 1:53 PM
  • Thanks,

    I can follow your explanation for the second example (uploading from the device to the server), but in the first example, the synchronization continues for more than 2 minutes after the last network activity (long after the dataset is serialized and sent to the device).

    Should I see this more as a SQL Compact/ IO problem then? maybe the insertion of the 5000 rows and the updating of the sync metadata just takes that long on my device.

    Thursday, August 11, 2011 2:11 PM
  • when the device receives the data during the download, it has to deserialize it back and apply the changes. the amount of memory on the device is usually the problem with this.

    it may also be the insertion of the 5k rows that may be the issue.

    have a look at this post: http://jtabadero.wordpress.com/2010/03/08/sync-framework-wcf-based-synchronization-for-offline-scenario-%e2%80%93-using-custom-dataset-serialization/

    it also points to some other blog post from the Sync Fx team.


    you dont necessarily follow the solution in the blog post, but that should give you an idea of what happens during the sync especially the serialization/deserialization and what data gets sent back and forth.
    • Edited by JuneT Thursday, August 11, 2011 2:44 PM
    Thursday, August 11, 2011 2:28 PM
  • Thank you very much, I'll have a look at this tomorrow morning
    Thursday, August 11, 2011 2:37 PM
  • I have tried this out and I must say the synchronization is already significantly faster (1min 8sec in stead of almost 3 minutes, in both cases), thank you!
    Friday, August 12, 2011 12:42 PM