V0.6.1

[Mimetis]

The version V0.6.1 is released !

Nuget packages : https://www.nuget.org/packages?q=Dotmim.Sync

This discussion thread will summarize some of the new functionalities available:

• Conflicts Improvements : V0.6.1 #415 (comment)
• Orchestrators public methods can use shared connection / transaction : V0.6.1 #415 (comment)
• Scope Info Queries improvements : V0.6.1 #415 (comment)
• Progress percentage and overall Rows Count : It's about time ! V0.6.1 #415 (comment)
• Upgrade version V0.6.1 #415 (comment)
• FIX : Transaction blocks server : Transaction blocks server #430
• FIX : Memory leak with batch mode : Memory leak with batch mode #426
• FIX : Snapshot + Reinitialize is not working as expected : Snapshot + Reinitialize won't work #422
• FIX : Snapshots download from api but not written to disk: Snapshots download from api but not written to disk #421
• FIX : TryConvertTo does not work on IOS when using dynamic TryConvertTo does not work on IOS when using dynamic #417 (Thanks @gaurakshay)

[Mimetis]

Conflicts

Conflicts algorithm has been reworked to be more efficient with more performances and less queries

Before this version, usually, for each conflict we had between 1 and 3 queries executed.
Now, the number of queries varies between 0 and 1.
Another improvement has been done on the internal algorithm to improve the SyncRow retrieval by primary key.
This improvement is really noticeable when you have a lot of conflicts to resolve.

That been said, here is a quick overview of the improvement with the v0.6.1 when dealing with 10 000 conflicts:

Using version v0.6:

Using version v0.6.1:

Worht it :)

[gentledepp]

Well, we had an in-depth analysis of the sync conflict resolution as well.
What we came to conclude is, that it could be even faster (i.e. requiring less database roundtrips):

Normally, the steps were:

1. try to insert/update/delete
2. if that did not work out for any reason, query the local row
3. resolve the sync conflict and update the local database

So I guess these are the 3 steps you were talking about.

The fastest approach to this would be:

1. for each change type (insert/update/delete), query the local rows that would be in conflict (i.e. the local timestamp is higher than the one now coming from the server
2. then, solve those issues in memory (because we alreadyloaded all conflicting rows, we can omit step 2
3. persist all those in-memory updated rows into the local database.

So while the old alrgorithm would scale linearly (for each conflict, do another 3 calls to the database => 1 conflict = 3 calls, 3 conflicts = 9 calls, etc.) the latter would do it nearly constantly (1 query for all conflicts, 1 update statement to solve them - of course, the latter is only true if batch-updates are indeed possible)

Does this make sense to you?

[Mimetis]

Indeed, this is almost what happens in the new version.

We do not query the conflict row, unless you want to make a comparison using the interceptor OnApplyChangeFailed
If you are relying only on default conflict resolution, no row is queried from the database.

For now, the update query is still 1-1 update (1 conflict is resolved using 1 update)

[Mimetis]

Orchestrators public methods

All the orchestrators public methods have been enhanced to allow you to use only one DbConnection for all your grouped commands.
It's especillay efficient for connection that does not use any connection pooling (like SQLite for instance) and it allows you to encapsulate everything in one single transaction.

Here is a comparison between the v0.6 and v0.6.1 methods signature (using BaseOrchestrator.GetLocalTimestampAsync() as an example):

// v0.6
public virtual Task<long> GetLocalTimestampAsync(
   CancellationToken cancellationToken = default, IProgress<ProgressArgs> progress = null) 

// v0.6.1
public virtual Task<long> GetLocalTimestampAsync(
   DbConnection connection = default, DbTransaction transaction = default, 
   CancellationToken cancellationToken = default, IProgress<ProgressArgs> progress = null)

Here is a small benchmark, using SQL Server, SQLite and MySQL:

// v0.6
for (int i = 0; i < 1000; i++)
{
    await orchestrator.GetEstimatedChangesCountAsync().ConfigureAwait(false);
    await orchestrator.ExistTableAsync(agent.Setup.Tables[0]).ConfigureAwait(false);
    await orchestrator.GetLocalTimestampAsync().ConfigureAwait(false);
    await orchestrator.GetSchemaAsync().ConfigureAwait(false);
    await orchestrator.GetChangesAsync().ConfigureAwait(false);
}

// v0.6.1
using (var c = agent.LocalOrchestrator.Provider.CreateConnection())
{
    c.Open();
    using (var t = connection.BeginTransaction())
    {
        for (int i = 0; i < 1000; i++)
        {
            await orchestrator.GetEstimatedChangesCountAsync(connection: c, transaction: t).ConfigureAwait(false);
            await orchestrator.ExistTableAsync(agent.Setup.Tables[0], connection: c, t: transaction).ConfigureAwait(false);
            await orchestrator.GetLocalTimestampAsync(connection: c, transaction: t).ConfigureAwait(false);
            await orchestrator.GetSchemaAsync(connection: c, transaction: t).ConfigureAwait(false);
            await orchestrator.GetChangesAsync(connection: c, transaction: t).ConfigureAwait(false);
        }
        t.Commit();
    }
    c.Close();
}

And here are the results:

SQL Server [Connection Pooling, Connection not shared]: 0:45.504
SQL Server [Connection Pooling, Connection shared]:        0:42.855

SQLite [Connection not shared]:  0:21.632
SQLite [Connection shared]:        0:8.525

MySQL Server [Connection Pooling, Connection not shared]: 3:59.944
MySQL Server [Connection Pooling, Connection shared]: 3:7.833

[Mimetis]

Scope Info Queries

All scope info queries have been cached and prepared to be more efficient.
This performance improvement affects only sync process that runs multiple times.
To see a real difference, here is sample iterating on a loop (5000 iteration) on some methods calling internally the sync scope queries

for (int i = 0; i < loop; i++)
{
    await orchestrator.ExistScopeInfoTableAsync(Dotmim.Sync.Builders.DbScopeType.Client, options.ScopeInfoTableName).ConfigureAwait(false);
    await orchestrator.ExistTableAsync(agent.Setup.Tables[0]).ConfigureAwait(false);
    await orchestrator.GetClientScopeAsync();
}

# v0.6
SQL Server [Connection Pooling, Connection not shared]: 0:12.592

SQLite [Connection not shared]: 1:4.161

#v0.6.1
SQL Server [Connection Pooling, Connection not shared]: 0:10.959
SQL Server [Connection Pooling, Connection shared]: 0:8.94

SQLite [Connection not shared]: 0:43.362
SQLite [Connection shared]: 0:3.901

[Mimetis]

Progress percentage: It's about time !

You have all requested it. The overall Percentage is finally here :)
You have even more. You will get the Rows Count to be applied and the Current Rows Count sent (or received).
And even more .... If you're in a web api mode you will get the Batches Count to download and the Batches Count to upload, and of course the Current Batches Count upload and downloaded.

Here is a quick overview of what you can get with this kind of code:

var localProgress = new SynchronousProgress<ProgressArgs>(s =>
{
    Console.ForegroundColor = ConsoleColor.Green;
    Console.WriteLine($"{s.PogressPercentageString}:\t{s.Message}");
    Console.ResetColor();
});

var s = await agent.SynchronizeAsync(localProgress);
Console.WriteLine(s);

TCP Mode

Here is the result of a TCP first call (initialization of a new empty database):

0%:     0b6dda78-21e7-433c-b346-93d32598fb2b:   Session Begins.
0%:     Client: Provisioned 9 Tables. Provision:Table, TrackingTable, StoredProcedures, Triggers.
57%:    AdventureWorks: [ProductDescription] [Total] Upserts:762. Deletes:0. Total:762.
59%:    AdventureWorks: [ProductCategory] [Total] Upserts:4041. Deletes:0. Total:4041.
61%:    AdventureWorks: [ProductModel] [Total] Upserts:128. Deletes:0. Total:128.
63%:    AdventureWorks: [Product] [Total] Upserts:295. Deletes:0. Total:295.
66%:    AdventureWorks: [Address] [Total] Upserts:450. Deletes:0. Total:450.
68%:    AdventureWorks: [Customer] [Total] Upserts:847. Deletes:0. Total:847.
70%:    AdventureWorks: [CustomerAddress] [Total] Upserts:417. Deletes:0. Total:417.
72%:    AdventureWorks: [SalesOrderHeader] [Total] Upserts:32. Deletes:0. Total:32.
75%:    AdventureWorks: [SalesOrderDetail] [Total] Upserts:542. Deletes:0. Total:542.
75%:    AdventureWorks: [Total] Upserts:7514. Deletes:0. Total:7514
77%:    Client: [ProductDescription] [Modified] Applied:(762) Total:(762/7514).
90%:    Client: [ProductCategory] [Modified] Applied:(4041) Total:(4803/7514).
91%:    Client: [ProductModel] [Modified] Applied:(128) Total:(4931/7514).
92%:    Client: [Product] [Modified] Applied:(295) Total:(5226/7514).
93%:    Client: [Address] [Modified] Applied:(450) Total:(5676/7514).
96%:    Client: [Customer] [Modified] Applied:(847) Total:(6523/7514).
98%:    Client: [CustomerAddress] [Modified] Applied:(417) Total:(6940/7514).
98%:    Client: [SalesOrderHeader] [Modified] Applied:(32) Total:(6972/7514).
100%:   Client: [SalesOrderDetail] [Modified] Applied:(542) Total:(7514/7514).
100%:   Client: [Total] Applied:7514. Conflicts:0.
100%:   0b6dda78-21e7-433c-b346-93d32598fb2b:   Session Ended.
Synchronization done.
        Total changes  uploaded: 0
        Total changes  downloaded: 7514
        Total changes  applied: 7514
        Total resolved conflicts: 0
        Total duration :0:0:4.209

As you can see, the 0% to 50% is not raised (so it could be seen as strange, weird or buggy :)) but since the client database has nothing to send, and obviously, the server has nothing to apply, 50% of the overall process is already done.

But let's see what happens now if we are inserting rows in both server and client databases:

0%:     847c6e93-8992-4218-88b3-741663d07533:   Session Begins.
12%:    Client: [ProductDescription] [Total] Upserts:10000. Deletes:0. Total:10000.
21%:    Client: [Address] [Total] Upserts:4. Deletes:0. Total:4.
23%:    Client: [Customer] [Total] Upserts:1. Deletes:0. Total:1.
30%:    Client: [SalesOrderDetail] [Total] Upserts:0. Deletes:16. Total:16.
30%:    Client: [Total] Upserts:10005. Deletes:16. Total:10021
30%:    AdventureWorks: [SalesOrderDetail] [Deleted] Applied:(13) Total:(13/10021).
52%:    AdventureWorks: [ProductDescription] [Modified] Applied:(8828) Total:(8841/10021).
54%:    AdventureWorks: [ProductDescription] [Modified] Applied:(10000) Total:(10013/10021).
54%:    AdventureWorks: [Address] [Modified] Applied:(4) Total:(10017/10021).
54%:    AdventureWorks: [Customer] [Modified] Applied:(0) Total:(10017/10021).
54%:    AdventureWorks: [Total] Applied:10017. Conflicts:4.
59%:    AdventureWorks: [ProductCategory] [Total] Upserts:10000. Deletes:0. Total:10000.
68%:    AdventureWorks: [Customer] [Total] Upserts:5. Deletes:0. Total:5.
75%:    AdventureWorks: [SalesOrderDetail] [Total] Upserts:0. Deletes:14. Total:14.
75%:    AdventureWorks: [Total] Upserts:10005. Deletes:14. Total:10019
75%:    Client: [SalesOrderDetail] [Deleted] Applied:(11) Total:(11/10019).
94%:    Client: [ProductCategory] [Modified] Applied:(7938) Total:(7949/10019).
99%:    Client: [ProductCategory] [Modified] Applied:(10000) Total:(10011/10019).
99%:    Client: [Customer] [Modified] Applied:(5) Total:(10016/10019).
99%:    Client: [Total] Applied:10016. Conflicts:4.
100%:   847c6e93-8992-4218-88b3-741663d07533:   Session Ended.
Synchronization done.
        Total changes  uploaded: 10021
        Total changes  downloaded: 10019
        Total changes  applied: 10016
        Total resolved conflicts: 4
        Total duration :0:0:2.652

Regarding the rows count, if you look this kind of line AdventureWorks: [ProductDescription] [Modified] Applied:(8828) Total:(8841/10021), you can see that we have:

• 8828 lines applied for table ProductDescription for a total of 8841 overall rows applied on a total to be applied of 10021

Ok now; we see that we have a more linear progress since the sync has rows to insert / update / delete in both databases.
Obviously, if we insert only rows on the client, it's not a surprised to have this kind of overall progress:

0%:     e05e0346-38f9-4ae9-9da4-84e612c5bd68:   Session Begins.
21%:    Client: [Address] [Total] Upserts:7. Deletes:0. Total:7.
23%:    Client: [Customer] [Total] Upserts:7. Deletes:0. Total:7.
30%:    Client: [SalesOrderDetail] [Total] Upserts:0. Deletes:12. Total:12.
30%:    Client: [Total] Upserts:14. Deletes:12. Total:26
41%:    AdventureWorks: [SalesOrderDetail] [Deleted] Applied:(12) Total:(12/26).
48%:    AdventureWorks: [Address] [Modified] Applied:(7) Total:(19/26).
54%:    AdventureWorks: [Customer] [Modified] Applied:(7) Total:(26/26).
54%:    AdventureWorks: [Total] Applied:26. Conflicts:0.
100%:   e05e0346-38f9-4ae9-9da4-84e612c5bd68:   Session Ended.
Synchronization done.
        Total changes  uploaded: 26
        Total changes  downloaded: 0
        Total changes  applied: 0
        Total resolved conflicts: 0
        Total duration :0:0:0.377

Http Mode

The Http mode is almost the same, a part that we have the Batches Count as well.
Here is an initialization sync:

0%:     Session Begins.
0%:     Getting Server Schema. Scope Name:DefaultScope.
0%:     Provisioned 9 Tables. Provision:Table, TrackingTable, StoredProcedures, Triggers.
10%:    Getting All Changes
42%:    Sending All Changes. Rows:0. Waiting Server Response...
57%:    Getting Batch Changes. (2/8).
60%:    Getting Batch Changes. (3/8).
62%:    Getting Batch Changes. (4/8).
65%:    Getting Batch Changes. (5/8).
67%:    Getting Batch Changes. (6/8).
70%:    Getting Batch Changes. (7/8).
72%:    Getting Batch Changes. (8/8).
78%:    [ProductDescription] [Modified] Applied:(8611) Total:(8611/67437).
81%:    [ProductDescription] [Modified] Applied:(17439) Total:(17439/67437).
84%:    [ProductDescription] [Modified] Applied:(26267) Total:(26267/67437).
86%:    [ProductDescription] [Modified] Applied:(30762) Total:(30762/67437).
89%:    [ProductCategory] [Modified] Applied:(7947) Total:(38709/67437).
92%:    [ProductCategory] [Modified] Applied:(15885) Total:(46647/67437).
95%:    [ProductCategory] [Modified] Applied:(23823) Total:(54585/67437).
98%:    [ProductCategory] [Modified] Applied:(31761) Total:(62523/67437).
99%:    [ProductCategory] [Modified] Applied:(34041) Total:(64803/67437).
99%:    [ProductModel] [Modified] Applied:(128) Total:(64931/67437).
99%:    [Product] [Modified] Applied:(295) Total:(65226/67437).
99%:    [Address] [Modified] Applied:(450) Total:(65676/67437).
99%:    [Customer] [Modified] Applied:(847) Total:(66523/67437).
99%:    [CustomerAddress] [Modified] Applied:(417) Total:(66940/67437).
99%:    [SalesOrderHeader] [Modified] Applied:(32) Total:(66972/67437).
100%:   [SalesOrderDetail] [Modified] Applied:(465) Total:(67437/67437).
100%:   [Total] Applied:67437. Conflicts:0.
100%:   Session Ended.
Synchronization done.
        Total changes  uploaded: 0
        Total changes  downloaded: 67437
        Total changes  applied: 67437
        Total resolved conflicts: 0
        Total duration :0:0:20.934

As you can see we have all the Rows Count, and Batches Count to download.
Obviously inserting rows on the client will generate this progress:

0%:     Session Begins.
0%:     Getting Server Scope. Scope Name:DefaultScope.
12%:    [ProductDescription] [Total] Upserts:10000. Deletes:0. Total:10000.
30%:    [Total] Upserts:10000. Deletes:0. Total:10000
40%:    Sending Batch Changes. Batches: (1/2). Rows: (8828/10000). Waiting Server Response...
50%:    Sending Batch Changes. Batches: (2/2). Rows: (10000/10000). Waiting Server Response...
65%:    Getting Batch Changes. (2/2).
94%:    [ProductCategory] [Modified] Applied:(7938) Total:(7938/10000).
100%:   [ProductCategory] [Modified] Applied:(10000) Total:(10000/10000).
100%:   [Total] Applied:10000. Conflicts:0.
100%:   Session Ended.
Synchronization done.
        Total changes  uploaded: 10000
        Total changes  downloaded: 10000
        Total changes  applied: 10000
        Total resolved conflicts: 0
        Total duration :0:0:5.887

Now, feel free to design a mobile application with a smart and cool progress bar (and easy to implement !)

Ok, my UI is not really shinny, you will be better than me I guess, but you get the idea !

[vunder]

Question regarding this. Does progress requires to have BatchSize != 0?

[Mimetis]

Works in all cases

[vunder]

Wow! Cannot wait to test new feature!

[Mimetis]

you've got it on the last v0.6.1 branch

[satpal123]

This is brilliant. Thanks @Mimetis

[Mimetis]

Upgrade DMS version

From now, DMS is implementing a new mechanism for in place upgrade (when going from version v0.5.7 to last version v0.6.1 for example)
Today, DMS will ensure that:

• A specific client, at least on version v0.5.7, will always continues to work with any version upgraded on the server.
• On the opposite side, there is no guarantee that an upgraded client (let's say v0.6.1) will work with a previous server version ( let's say v0.5.7 for example)

So far, an upgrade path should always be:

• Upgrade the server (server goes from v0.5.7 to v0.6.1 for example). All clients will continue to work with their in place "non upgraded" version (v0.5.7 or v0.6.0 for instance)
• Then upgrade all clients to the last version (v0.6.1)

Upgrading Server or Client

For various reasons, it could happens that a version will have to regenerate something, like a trigger or a stored procedure etc...
For this reason, once you have upgraded DMS to the last version, you need to call a new method called UpgradeAsync()
This method will ensure that the database is up to date with the last features.

For instance, the v0.6.1 needs to change a stored procedure (all the Table_Initialize stored procedure)

That's why, once you have referenced the V0.6.1 in you web api, a good solution would be to create a console application, and call the UpgradeAsync() method:

var serverProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(serverDbName));
var remoteOrchestrator = new RemoteOrchestrator(serverProvider, options, setup);

// Upgrade to last version
if (await remoteOrchestrator.NeedsToUpgradeAsync())
     await remoteOrchestrator.UpgradeAsync();

Once you're done, you can check you server database scope_info_server table and check the sync_scope_version column:

As you can see in the previous screenshot, we are set to the last v0.6.1 version.
(And by the way, the UpgradeAsync() method has updated the Table_Initialize stored procedure)

Obviously, you should do the same on the client side.