locked
After Sync Compact Database is 2-3 times slower as without (SQL Server / CE Sync Scenario with Entity Framework) RRS feed

  • Question

  • Hi,

    we use Sync Framework 2.1 with SQL Server 2008 R2 and Compact Server 3.5 SP 2. 

    We have one SQL Server and a lot of CE databases as clients. Regulary the new data in SQL Server has to be deployed to the clients. We use DbServerSyncProvider as SyncProvider for SQL Server and the SqlCeClientSyncProvider for Compact Server. For every table we have one SyncAdapter that holds the SELECT queries to get the added and updated values. We only need download direction (SQL -> CE) and deletes doesn't matter.

    The sync process runs smoothly. The compact database has all the data as expected. Additionally there 're the 3 sync system tables and 3 system columns in every table.

    The mapping with Entity Framework works great. 

    But the database access in the application with Entity Framework is 2 to 3 times slower as without the complete sync process! We already tested a compact database with the same mapping but without sync. In my opinion it has to be synchronized database.

    I thought that it comes from the activated change tracking feature in Compact Server. So I after complete sync I disabled the change tracking for every table (with SqlCeChangeTracking). It's a few seconds faster but still slower 2 times as without sync.

    Is there any other "feature" in Compact Server that can slow down the hole database?

    If you need some code I can post it.

    Regards,

    Christian

    Friday, January 13, 2012 1:14 PM

Answers

  • Have you compared the schemas for the two scenarios? I think that Sync Framework does not create indexes for you, you have to do that manually.
    Please mark as answer, if this was it. Visit my SQL Server Compact blog
    • Marked as answer by steinch Monday, January 16, 2012 1:06 PM
    Monday, January 16, 2012 12:17 PM

All replies

  • It's seems, that sync databases are automatically upgraded when Sync Framework connects to them. ( http://msdn.microsoft.com/en-us/library/dd937768(v=sql.110).aspx#_sqlce35sp2_compat )

    So I have to disable the "new feature" for every single table like this:

    var ceConn = new SqlCeConnection(connectionstring);
    ceConn.Open();
    var tables = new List<string> { "Table1", "Table2" };
    using (var changeTracking = new SqlCeChangeTracking(ceConn))
    {
    	foreach (var syncTable in tables)
        {
            changeTracking.DisableTracking(syncTable);
        }
    }
    //do not throw away Connection!
    ceConn.Close();
    
    var entityBuilder = new EntityConnectionStringBuilder();
    entityBuilder.Provider = "System.Data.SqlServerCe.3.5";
    entityBuilder.Metadata = @"metadata...";
    entityBuilder.ProviderConnectionString = ceConn.ConnectionString;
    var ctx = new EntityContext(entityBuilder.ToString());
    

    Now the application is only a few ticks slower than without sync. 

    Why there is no option to disable the whole feature for the database?

    Friday, January 13, 2012 4:06 PM
  • You are using the old hub and spoke providers which are unrelated to the documentation you linked to.

    I'm trying to understand the problem you are having.  Are you saying that the data access performance via Entity Framework is significantly slower on synced databases than the same database that is not sync-enabled?

    -Jesse

     

    Friday, January 13, 2012 9:20 PM
  • Hi,

    the reason for the hub and spoke providers are the need of offline scenario. All clients connects at different times with the server. And we only need the download direction. So I need only a few lines of code for every table and I have my own changed tracking via columns like "CreatedAt, UpdateAt, ...". Do you have a suggestion for other providers? 

    The problem is like you said. For test proposes I have two compact databases with ~30 tables and at all 100,000 entries. Both have the same mapping with Entity Framework. One was created by sync framework and the other was manually created.

    • If I'm not disable change tracking, like in the first post, the access is 2-3 times slower. 
    • If I disable change tracking for every table with the same connection as I used for the Entity Context (second post) the access is closely fast as with "not synced" database.

    So my question is, is there any possiblity to disable the whole change tracking feature via Compact Server API?

    Or is there something wrong in my process or understanding of sync framework? 

     

    Christian

    Saturday, January 14, 2012 1:53 PM
  • Have you compared the schemas for the two scenarios? I think that Sync Framework does not create indexes for you, you have to do that manually.
    Please mark as answer, if this was it. Visit my SQL Server Compact blog
    • Marked as answer by steinch Monday, January 16, 2012 1:06 PM
    Monday, January 16, 2012 12:17 PM
  • Hi Erik,

    that's it! :-) If I create the indexes for foreign keys, than both databases (not synced and synced) have the same speed.

    If I disable additionally the change tracking like above, than the synced database is 20% faster! 

    Thanks a lot for your tip! 

    Regards,

    Christian

    Monday, January 16, 2012 1:06 PM
  • christian,

    out of curiousity, is it the write operation or read operation that is slowed down?

    Tuesday, January 17, 2012 1:45 AM
  • Hi June,

    it's the read operation. In this case I've a simple master details dialog, which reads the most of the data.

    After applied the tip of erik I've found that I missed the foreign keys in synced database, too.

    So I run a sql script after intial sync process. And it runs all smoothly :-)

    By the way, the compact server toolbox from erik helps a lot!

    Regards,

    Christian 

    Tuesday, January 17, 2012 8:55 AM
  • +1 to Erik's toolbox

    interesting finding for you though. i would assume the write operations to be slowed down because of the extra overhead with change tracking.

    if the problem is with FKs, then you should have the same performance with or without change tracking.

    not unless EF context is actually loading the system tables associated with change tracking.

    well, it's working now and that's what matters :)

    Tuesday, January 17, 2012 10:14 AM
  • Great investigation process guys.

    Cheers

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, January 17, 2012 10:08 PM