none
Help with SyncFX performance!! RRS feed

  • Question

  • OS: Windows XP SP3 fully patched
    DB: SQL Server Express 2008
    Technologies: .NET 4.0, WPF, ADO.NET, SyncFX 2.0

    I am working on an application where we have a relatively small DB. I can't imagine it going over 250MB for example. Right now, it is 38MB.

    We have 13 tables. Most are pretty small (< 100 rows). 2 of them will get kind of big (~2M rows). There are no stored procs involved at this point. Its all using ADO.NET DataSets / DataTables / primary keys / foreign keys, etc.
    No triggers. Not much more then a few unique constraints and identity constraints for the primary keys.

    My current table fill code is:

    DateTime dtStart = DateTime.Now;

    taTemplateCategories.Fill(ds.tblTemplateCategories);
    taScriptCategories.Fill(ds.tblScriptCategories);
    taFieldCategories.Fill(ds.tblFieldCategories);
    taScript.Fill(ds.tblScript);
    taField.Fill(ds.tblField);
    taTemplate.Fill(ds.tblTemplate);
    taTemplateSet.Fill(ds.tblTemplateSet);
    taTemplateSets.Fill(ds.tblTemplateSets);
    taTemplateFieldReferences.Fill(ds.tblTemplateFieldReferences);
    taFieldFieldReferences.Fill(ds.tblFieldFieldReferences);
    taBaseline.Fill(ds.tblBaseline);
    taMarkup.Fill(ds.tblMarkup);

    DateTime dtEnd = DateTime.Now;
    TimeSpan ts = dtEnd - dtStart;

    System.Diagnostics.Debug.WriteLine("FILL TIME: " + ts.TotalMilliseconds.ToString());

    This code above is NOT using SyncFX, but my boss seems to think SyncFX will improve performance. I am seeing the exact opposite.

    The above code, *NOT* using SyncFX takes:

    578ms initial startup
    220ms subsequent "reloads"

    When I switch to SyncFX, my reload code becomes:

    DateTime dtStart = DateTime.Now;

    xxxCacheSyncAgent syncAgent = new xxxCacheSyncAgent();
    Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();

    taTemplateCategories.Fill(ds.tblTemplateCategories);
    taScriptCategories.Fill(ds.tblScriptCategories);
    taFieldCategories.Fill(ds.tblFieldCategories);
    taScript.Fill(ds.tblScript);
    taField.Fill(ds.tblField);
    taTemplate.Fill(ds.tblTemplate);
    taTemplateSet.Fill(ds.tblTemplateSet);
    taTemplateSets.Fill(ds.tblTemplateSets);
    taTemplateFieldReferences.Fill(ds.tblTemplateFieldReferences);
    taFieldFieldReferences.Fill(ds.tblFieldFieldReferences);
    taBaseline.Fill(ds.tblBaseline);
    taMarkup.Fill(ds.tblMarkup);

    listBox1.ItemsSource = null;// ds.tblTest.Rows;
    listBox1.ItemsSource = ds.tblTemplate.Rows;

    DateTime dtEnd = DateTime.Now;
    TimeSpan ts = dtEnd - dtStart;

    System.Diagnostics.Debug.WriteLine("RELOAD FILL: " + ts.TotalMilliseconds.ToString());

    However, NOW...

    10562ms initial startup
    2625ms subsequent "reloads"

    The SDF created was only 27MB.

    As you can see... startup time goes from 0.5seconds -> 10seconds!!! INSANE!!!
    Subsequent reloads goes from 0.2seconds -> 2seconds!!! INSANE!!!

    I thought the point of SyncFX was that it was supposed to be faster.

    I understand that the first time where it created the snapshot, the delay would be bigger since its downloading everything. But I thought subsequent loads would be much, much, much faster.

    But the "old" way where it just grabbed the records from the SQL server all the time took less time then to download all the records and sync the local cache.

    Am I doing something wrong? Or is this to be expected?

    I can see SyncFX is giving me an offline copy of the DB, but with performance so bad, surely there is a better solution?

    I am just using the Ado.Net add datasource wizard and not tweaking any of the generated code. I did update the cache agent to make it bidirectional.

     public partial class xxxCacheSyncAgent
     {
      partial void OnInitialized()
      {
       tblBaseline.SyncDirection = SyncDirection.Bidirectional;
       tblField.SyncDirection = SyncDirection.Bidirectional;
       tblFieldCategories.SyncDirection = SyncDirection.Bidirectional;
       tblFieldFieldReferences.SyncDirection = SyncDirection.Bidirectional;
       tblMarkup.SyncDirection = SyncDirection.Bidirectional;
       tblScript.SyncDirection = SyncDirection.Bidirectional;
       tblScriptCategories.SyncDirection = SyncDirection.Bidirectional;
       tblTemplate.SyncDirection = SyncDirection.Bidirectional;
       tblTemplateCategories.SyncDirection = SyncDirection.Bidirectional;
       tblTemplateFieldReferences.SyncDirection = SyncDirection.Bidirectional;
       tblTemplateSet.SyncDirection = SyncDirection.Bidirectional;
       tblTemplateSets.SyncDirection = SyncDirection.Bidirectional;
       tblTokenSubst.SyncDirection = SyncDirection.Bidirectional;

       ((xxxCacheClientSyncProvider)LocalProvider).AddHandlers();
      }

    Other then that, I don't think I tweaked anything else.

    Any ideas?

    Friday, July 30, 2010 9:15 PM

All replies

  • i've already replied on your other thread on what's happening under the hood when you call sync. But to elaborate further, here's the amount of commands the bidirectional sync is doing:

    For each table you sync:

    1 x query to grab an anchor

    Change Enumeration:

    1 x SelectIncrementalInsert

    1 x SelectIncrementalUpdate

    1 x SelectIncrementalDelete

    3 Queries above multiplied by the number of tables = 13 x 3 = 39 Queries just on one side. Since it's bidirectional, it's twice which is = 78 queries.

    That's just for change enumeration (checking if there is any change at all)

    For Change application:

    Assuming you have 1 insert for each table on each side, that's 1 Insert Query per table = 13 Inserts, for both sides = 26. Do the math if you have updates and deletes are well and the number of rows involved.

    You can enable Sync Tracing to see what Sync Fx is doing and also use SQL Profiler to check on what queries are going against the database.

    So to answer your query if Sync Fx makes your sync faster compared to your old solution, the answer is obviously NO since you're app is actually doing more. On the other hand, you will find that Sync Fx will be a more mature API for doing syncs compared to rolling out your own specially when provisioning, conflict resolution, and other factors comes into play since these functionalities are already in the Framework.

    Friday, July 30, 2010 11:18 PM
    Moderator