xamarin forms azure mobile apps slow sync

Question

I'm using Azure Mobile App with Xamarin.Forms to create an offline capable mobile app.

My solution is based on https://adrianhall.github.io/develop-mobile-apps-with-csharp-and-azure/chapter3/client/

Here is the code that I use for offline sync :

public class AzureDataSource
    {
        private async Task InitializeAsync()
        {
            // Short circuit - local database is already initialized
            if (client.SyncContext.IsInitialized)
            {
                return;
            }

            // Define the database schema
            store.DefineTable<ArrayElement>();
            store.DefineTable<InputAnswer>();
            //Same thing with 16 others table
            ...

            // Actually create the store and update the schema
            await client.SyncContext.InitializeAsync(store, new MobileServiceSyncHandler());
        }

        public async Task SyncOfflineCacheAsync()
        {
            await InitializeAsync();

            //Check if authenticated
            if (client.CurrentUser != null)
            {
                // Push the Operations Queue to the mobile backend
                await client.SyncContext.PushAsync();

                // Pull each sync table
                var arrayTable = await GetTableAsync<ArrayElement>();
                await arrayTable.PullAsync();

                var inputAnswerInstanceTable = await GetTableAsync<InputAnswer>();
                await inputAnswerInstanceTable.PullAsync();

                //Same thing with 16 others table
                ...
            }
        }

        public async Task<IGenericTable<T>> GetTableAsync<T>() where T : TableData
        {
            await InitializeAsync();
            return new AzureCloudTable<T>(client);
        }
    }
    public class AzureCloudTable<T>
    {
        public AzureCloudTable(MobileServiceClient client)
        {
            this.client = client;
            this.table = client.GetSyncTable<T>();
        }

        public async Task PullAsync()
        {
            //Query name used for incremental pull
            string queryName = $"incsync_{typeof(T).Name}";

            await table.PullAsync(queryName, table.CreateQuery());
        }
    }

The problem is that the syncing takes a lot of time even when there isn't anything to pull (8-9 seconds on Android devices and more than 25 seconds to pull the whole database).

I looked at Fiddler to find how much time takes the Mobile Apps BackEnd to respond and it is about 50 milliseconds per request so the problem doesn't seem to come from here.

Does anyone have the same trouble ? Is there something that I'm doing wrong or tips to improve my sync performance ?

Answer 1

Our particular issue was linked to our database migration. Every row in the database had the same updatedAt value. We ran an SQL script to modify these so that they were all unique.

This fix was actually for some other issue we had, where not all rows were being returned for some unknown reason, but we also saw a substantial speed improvement.

---

Also, another weird fix that improved loading times was the following.

After we had pulled all of the data the first time (which, understandably takes some time) - we did an UpdateAsync() on one of the rows that were returned, and we did not push it afterwards.

We've come to understand that the way offline sync works, is that it will pull anything that has a date newer than the most recent updated at. There was a small speed improvement associated with this.

---

Finally, the last thing we did to improve speed was to not fetch the data again, if it already had cached a copy in the view. This may not work for your use case though.

public List<Foo> fooList = new List<Foo>

public void DisplayAllFoo()
{
    if(fooList.Count == 0)
        fooList = await SyncClass.GetAllFoo();

    foreach(var foo in fooList)
    {
        Console.WriteLine(foo.bar);
    }
}

---

Edit 20th March 2019: With these improvements in place, we are still seeing very slow sync operations, used in the same way as mentioned in the OP, also including the improvements listed in my answer here.

I encourage all to share their solutions or ideas on how this speed can be improved.

Answer 2

One of the reasons for the slow Pull() is when more than (10) rows get the same UpdatedAt value. This happens when you update the rows at once, for example running an SQL command.

One way to overcome this is to modify the default trigger on the tables. To ensure every row gets a unique UpdateAt, we did something like this:

ALTER TRIGGER [dbo].[TR_dbo_Items_InsertUpdateDelete] ON [dbo].[TableName]
AFTER INSERT, UPDATE, DELETE
AS
     BEGIN

         DECLARE @InsertedAndDeleted TABLE
         (
              Id        NVARCHAR(128) 
         );
         DECLARE @Count     INT, 
                 @Id        NVARCHAR(128);

         INSERT INTO @InsertedAndDeleted
                SELECT Id
                FROM inserted;
         INSERT INTO @InsertedAndDeleted
                SELECT Id
                FROM deleted
                WHERE Id NOT IN
                (
                    SELECT Id
                    FROM @InsertedAndDeleted
                );

         --select * from @InsertedAndDeleted;
         SELECT @Count = Count(*)
         FROM @InsertedAndDeleted;

         -- ************************ UpdatedAt ************************
         -- while loop
         WHILE @Count > 0
         BEGIN
             -- selecting
             SELECT TOP (1) @Id = Id
             FROM @InsertedAndDeleted;

             -- updating
             UPDATE [dbo].[TableName]
                    SET UpdatedAt = Convert(DATETIMEOFFSET, DateAdd(MILLISECOND, @Count, SysUtcDateTime()))
             WHERE Id = @Id;

             -- deleting
             DELETE FROM @InsertedAndDeleted
             WHERE id = @Id;

             -- counter
             SET @Count = @Count - 1;
         END;
     END;