locked
Sync Provider for Devices 1 (SP1 + Hotfix) slow during Download state RRS feed

  • Question

  • Im syncing 270 tables in a hub-spoke environment without batching, everything works great except for speeeeeeeeeeed (close to 3 hours to complete).  I am aware this is a old and possibly not supported set of drivers, but its worth a shot.  This is not a network bandwidth issue, the total sync time improves with speed of mobile processor.

    I am monitoring the mobile's SyncStage in the SessionProgress event and it seems to spend a lot of time on the download side of things.  From watching network traffic on mobile and server, I can see traffic spikes every 35 seconds (traffic pre table). 

    My problem is there is no new data to be transffered, so spending 35 seconds per a table to update nothing seems wrong.  My poor customers batteries are flat before all tables sync :-(

    The hotfix http://support.microsoft.com/kb/973058 did not improve my timings (there is nothing to update, so I expected this)

     

    My only guess is that Sync is comparing database column structure between server and device (table structure does not change, just want to confirm I can turn off this checking).  Guessing aside, does anyone know what I can do (a mystical setting to check)?

     

    To quickly detail my environment :

    Mobile OS : Windows Mobile 6.1 (400Mhz and 800Mhz devices)

    Mobile Software : Sync V1.0 (SP1 + Hotfix)

    Server Software : Sync V2.1

    Thursday, February 24, 2011 7:04 AM

Answers

  •  

    Hi Omad,

    there was a known perf issue when sync-ing with tables with no changes, i.e. even if there are no changes, we still need to check to detect and enmerate changes from it, this will cause the perf issues with running on devices where the processor is generally slow.

    we recommended to add logic to detect the changed table and dynamically construct the sync configuration with the tables that need to be synced ( i.e. skip the empty tables ), to improve the sync performance under such cases.

    there are many ways to get this implmented, here is one sample for it. you may find more efficient way to do so if you can add logic to your application or your database by tracking the changed tables seperatedly. hope this helps.

     

    Construct the sync agent’s sync group with only changed tables

    private SyncAgent PrepareAgent(List<SyncTable> clientTables)

    {

    SyncGroup agentGroup = new SyncGroup("ChangedGroup");

     

    SyncAgent changedAgent = new SyncAgent();

     

    foreach (SyncTable table in clientTables)

    {

    table.SyncGroup = agentGroup;

    this.ClearParent(table);

    changedAgent.Configuration.SyncTables.Add(table);

    }

     

    return changedAgent;

    }

     

     

    Get changed tables from local client db

     

    private List<SyncTable> GetChangedTablesOnClient()

    {

    // Initialize the list of tables to synchronize

    List<SyncTable> tables = new List<SyncTable>();

     

    // Open connection to the database

    if (clientSyncProvider.Connection.State == System.Data.ConnectionState.Closed)

    {

    clientSyncProvider.Connection.Open();

    }

     

    // Prepare Command

    SqlCeCommand command = new SqlCeCommand();

    command.Connection = (SqlCeConnection)clientSyncProvider.Connection;

    command.Parameters.Add(new SqlCeParameter("@LCSN", System.Data.SqlDbType.BigInt));

     

    // Retreive changed tables

    foreach (string tableName in this.clientTables.Keys)

    {

    // Build a command for this table

    command.CommandText = GetQueryString("[" + tableName + "]");

    // Execute the command

    long lcsn = GetLastSyncCsn(command.Connection, tableName);

    command.Parameters["@LCSN"].Value = lcsn;

    int result = (int)command.ExecuteScalar();

    // If the table contains changed data

    if (result > 0)

    {

    // then add it to the synchronization list

    tables.Add(this.clientTables[tableName]);

    }

    }

    // Clean up

    command.Dispose();

     

    return tables;

    }

     

    Get the changed tables from the server side

    // service method

        //

        public Collection<string> GetServerChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)

        {

            Collection<string> tables = new Collection<string>();

     

            SyncContext changes = _serverSyncProvider.GetChanges(groupMetadata, syncSession);

            if (changes.DataSet.Tables.Count > 0)

            {

                foreach (DataTable table in changes.DataSet.Tables)

                {

                    if (table.Rows.Count > 0)

                    {

                        tables.Add(table.TableName);

                    }

                }

            }

     

            return tables;

        }

     

        // client side methods to get the server changes by calling the service proxy method

        //

        private string[] GetServerChanges(List<SyncParameter> parameters)

        {

            SyncGroupMetadata metaData = this.GetMetadata();

            SyncSession session = new SyncSession();

            session.ClientId = clientSyncProvider.ClientId;

            session.SyncParameters = new SyncParameterCollection();

            if (parameters != null)

            {

                foreach (SyncParameter parameter in parameters)

                {

                    session.SyncParameters.Add(parameter);

                }

            }

     

            return this.proxyExtension.GetServerChanges(metaData, session);

        }

     

        private SyncGroupMetadata GetMetadata()

        {

            SyncGroupMetadata groupMetadata = new SyncGroupMetadata();

     

            foreach (string tableName in clientTables.Keys)

            {

                SyncTableMetadata tableMetadata = new SyncTableMetadata();

                tableMetadata.LastReceivedAnchor = clientSyncProvider.GetTableReceivedAnchor(tableName);

                tableMetadata.TableName = tableName;

                groupMetadata.TablesMetadata.Add(tableMetadata);

            }

     

            return groupMetadata;

        }

     

     

     

    Friday, February 25, 2011 3:19 PM

All replies

  •  

    Hi Omad,

    there was a known perf issue when sync-ing with tables with no changes, i.e. even if there are no changes, we still need to check to detect and enmerate changes from it, this will cause the perf issues with running on devices where the processor is generally slow.

    we recommended to add logic to detect the changed table and dynamically construct the sync configuration with the tables that need to be synced ( i.e. skip the empty tables ), to improve the sync performance under such cases.

    there are many ways to get this implmented, here is one sample for it. you may find more efficient way to do so if you can add logic to your application or your database by tracking the changed tables seperatedly. hope this helps.

     

    Construct the sync agent’s sync group with only changed tables

    private SyncAgent PrepareAgent(List<SyncTable> clientTables)

    {

    SyncGroup agentGroup = new SyncGroup("ChangedGroup");

     

    SyncAgent changedAgent = new SyncAgent();

     

    foreach (SyncTable table in clientTables)

    {

    table.SyncGroup = agentGroup;

    this.ClearParent(table);

    changedAgent.Configuration.SyncTables.Add(table);

    }

     

    return changedAgent;

    }

     

     

    Get changed tables from local client db

     

    private List<SyncTable> GetChangedTablesOnClient()

    {

    // Initialize the list of tables to synchronize

    List<SyncTable> tables = new List<SyncTable>();

     

    // Open connection to the database

    if (clientSyncProvider.Connection.State == System.Data.ConnectionState.Closed)

    {

    clientSyncProvider.Connection.Open();

    }

     

    // Prepare Command

    SqlCeCommand command = new SqlCeCommand();

    command.Connection = (SqlCeConnection)clientSyncProvider.Connection;

    command.Parameters.Add(new SqlCeParameter("@LCSN", System.Data.SqlDbType.BigInt));

     

    // Retreive changed tables

    foreach (string tableName in this.clientTables.Keys)

    {

    // Build a command for this table

    command.CommandText = GetQueryString("[" + tableName + "]");

    // Execute the command

    long lcsn = GetLastSyncCsn(command.Connection, tableName);

    command.Parameters["@LCSN"].Value = lcsn;

    int result = (int)command.ExecuteScalar();

    // If the table contains changed data

    if (result > 0)

    {

    // then add it to the synchronization list

    tables.Add(this.clientTables[tableName]);

    }

    }

    // Clean up

    command.Dispose();

     

    return tables;

    }

     

    Get the changed tables from the server side

    // service method

        //

        public Collection<string> GetServerChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)

        {

            Collection<string> tables = new Collection<string>();

     

            SyncContext changes = _serverSyncProvider.GetChanges(groupMetadata, syncSession);

            if (changes.DataSet.Tables.Count > 0)

            {

                foreach (DataTable table in changes.DataSet.Tables)

                {

                    if (table.Rows.Count > 0)

                    {

                        tables.Add(table.TableName);

                    }

                }

            }

     

            return tables;

        }

     

        // client side methods to get the server changes by calling the service proxy method

        //

        private string[] GetServerChanges(List<SyncParameter> parameters)

        {

            SyncGroupMetadata metaData = this.GetMetadata();

            SyncSession session = new SyncSession();

            session.ClientId = clientSyncProvider.ClientId;

            session.SyncParameters = new SyncParameterCollection();

            if (parameters != null)

            {

                foreach (SyncParameter parameter in parameters)

                {

                    session.SyncParameters.Add(parameter);

                }

            }

     

            return this.proxyExtension.GetServerChanges(metaData, session);

        }

     

        private SyncGroupMetadata GetMetadata()

        {

            SyncGroupMetadata groupMetadata = new SyncGroupMetadata();

     

            foreach (string tableName in clientTables.Keys)

            {

                SyncTableMetadata tableMetadata = new SyncTableMetadata();

                tableMetadata.LastReceivedAnchor = clientSyncProvider.GetTableReceivedAnchor(tableName);

                tableMetadata.TableName = tableName;

                groupMetadata.TablesMetadata.Add(tableMetadata);

            }

     

            return groupMetadata;

        }

     

     

     

    Friday, February 25, 2011 3:19 PM
  • Yunwen,

    You just earned yourself a box of beer!

    I only implemented the server side checking and replaced all Collection<string> with string[].  Everything works great!

    Average sync per a device has gone down from 3 hours to 5 minutes!!!!!

    Thank you 

    Tuesday, March 1, 2011 4:09 AM
  • Hello Omad, hello Yunwen!

    Thank you for sharing this piece of code, it is always good to see some real implementations.

    I tried and implemented the server side checking based on Yunwens code and it boosts performance of our sync 30-40% if lots of download-only tables are involved.

    What I would like to do now is introduce the local client side checking too. Looking at the code I miss some methods. What about ClearParent(..), GetQueryString(..), GetLastSyncCsn(..)? Are these custom methods of your own or are they related to another version of SyncFX? I am developing for WinMo 6.1 devices using SyncServices for Devices 1. Is it still possible to implement local change checking?

    Tracking updated tables on my own does not feel like a good solution. Isn't his what the Sql Ce changetracking is meant for? As a last resort I could of course hook into my DataLayer but I would rather not.

     

    Regards,

    Andreas

    Wednesday, March 2, 2011 4:17 PM
  • sorry but I guess i missed a couple dependent methods here. below are the two missing methods for the client side. As you have noticed, the query is directly call to the SqlCe tracking tables so please keep this in mind they could be changed without notifications. currently we don't have a API for this functionalities.

    private string GetQueryString(string tableName)

            {

                return String.Format(@"select count(*) from {0} Tbl where (((Tbl.__sysInsertTxBsn IS NOT NULL) AND ((Tbl.__sysInsertTxBsn NOT IN (select __sysTxBsn from __sysTxCommitSequence)  AND Tbl.__sysInsertTxBsn > @LCSN) OR (exists(select __sysTxBsn from __sysTxCommitSequence where Tbl.__sysInsertTxBsn = __sysTxBsn  AND __sysTxCsn > @LCSN))))

                                    OR ((Tbl.__sysChangeTxBsn IS NOT NULL) AND ((Tbl.__sysChangeTxBsn NOT IN (select __sysTxBsn from __sysTxCommitSequence)  AND Tbl.__sysChangeTxBsn > @LCSN) OR (exists(select __sysTxBsn from __sysTxCommitSequence where Tbl.__sysChangeTxBsn = __sysTxBsn AND __sysTxCsn > @LCSN)))))", tableName);

            }

     

     

       private long GetLastSyncCsn(SqlCeConnection connection, string tableName)

            {

                SqlCeCommand command = null;

                SqlCeDataReader reader = null;

                long num3;

                long num = 0L;

                try

                {

                    command = new SqlCeCommand();

                    command.Connection = connection;

                    command.CommandText = string.Format("select SentAnchor from {0} Where TableName = @tablename", "__sysSyncArticles");

                    command.Parameters.AddWithValue("@tablename", tableName);

                    reader = command.ExecuteReader();

                    bool flag = true;

                    byte[] buffer = new byte[60];

                    while (reader.Read())

                    {

                        if (!reader.IsDBNull(0))

                        {

                            reader.GetBytes(0, 0L, buffer, 0, 8);

                            long num2 = BitConverter.ToInt64(buffer, 0);

                            if (flag)

                            {

                                num = num2;

                            }

                            flag = false;

                            if (num2 < num)

                            {

                                num = num2;

                            }

                        }

                    }

                    num3 = num;

                }

                finally

                {

                    if (command != null)

                    {

                        command.Dispose();

                    }

                    if (reader != null)

                    {

                        reader.Dispose();

                    }

                }

                return num3;

            }

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, March 3, 2011 2:54 AM
  • hi Yunwen,

    just a clarification, the clientTables referenced in the foreach loop in GetMetadata is the full list of tables and not just the changed tables from the client right?

    otherwise if it's just the list of changed tables on the client side, then the client would miss out on the changes from all other tables in the server.

    e.g., assuming an originally 100-table sync group, with only 10 tables with changes on the client, if only the 10 tables is passed to the server, then the client would miss the changes from the other 90 tables.

    thanks!

    junet

     

    Thursday, March 3, 2011 3:40 AM
  • yes, you should evaluate every table that is orgianly set up for the sync.

     

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, March 3, 2011 4:49 AM
  • Hi Yunwen,

    great thanks!

    I did slightly modify the GetLastSyncCsn(.) method because it seemed to always add my tables to changedTables list, even those that are download-only and therefore should never have any local changes.

    but again - great stuff, thank you so much for sharing. can't wait what the final performance improvement will be in %.

    One quick question:
    if I would also change the SyncDirection of the table depending on local changes only, server changes only or both, could that improve something or does this even break internal changetracking records? Don't have the time to test it out in every detail right now.

    Andreas

    Thursday, March 3, 2011 9:47 AM
  • change the syncDirection is allowed. but I would suggest to make the right/best decision when design the application so that you can group your tables not only based on the relationships, but also can be done by the sync directions so that you can reduce the size of the sync group to improve the performance.

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, March 3, 2011 4:00 PM
  • Hi again,

    I was re-working my whole sync logic based on the proposed solutions here. Until now I managed to boost performance about 40%. The check for server-side changes does take long, so you really win the more tables you want to sync.

    One warning to others - manipulating the SyncDirection is not the best idea. I wanted to get rid of phantom uploads on e.g. the first sync. Therefore I changed the Direction on the first sync to DownloadOnly (you have to specify anything, otherwise the tables wont't get created, in my case crashing the app. This speeds up the first sync as I would have guessed, but afterwards inserts to this table were not correctly tracked any more (i.e. no longer uploaded to the server).

    Regards,

    Andreas

    Thursday, March 17, 2011 1:13 PM
  • Hi there,

    Can you confirm where each of the code blocks in this thread need to be inserted? We are specifically synchronising via a WCF service as well.

    Thanks in advance,

    -Pat Ramadass

    Wednesday, April 27, 2011 11:36 PM
  • Please provide information where these code blocks should be inserted and a definition of the overarching class

    For instance, in the method GetServerChanges there is a call to the method this.GetMetadata().  What is this?


    Howard P. Weiss

    Friday, August 15, 2014 3:04 PM
  • this code will work with the older offline sync providers (DbServerSyncProvider/SqlCeClientSyncProvider/SyncAgent).

    the GetMetadata() is actually in the code snippet above and is used to retrieve sync anchor information.

    Tuesday, August 19, 2014 1:01 AM
  • What are the equivalents of SentAnchor and ReceivedAnchor for SqlSyncProvider?


    Howard P. Weiss

    Tuesday, August 19, 2014 1:56 PM
  • nothing, the newer providers stores the sync metadata differently (sync knowledge).

    you can do something similar to the server get changes in the above code. it's essentially simulating a GetChanges and checking if there was a change for each table.

    if you have a large table, that's a rather expensive operation though as it will query the actual tables for changes similar to what it does during the sync.

    Wednesday, August 20, 2014 1:14 AM