none
Average performance of sync framework 1.0 sp1 for devices RRS feed

  • Question

  • Hi, im wondering what kind of performance can i expect from sync framework 1.0 sp1 for devices.

    Im using sql server 2008 in the server side, and sql compact for devices 3.5 sp2 on the emulator (thats where im getting those times)

    My database is about 2,5 MB for the compact database. It has about 50 tables.

    Im using WCF to connect the client device and the server (though i've monitored the connection and there is very little data sent or received, so i discarded that this was my bottleneck)

    Currently, with no change to sync (server and client databases have the same data), the CacheSyncAgent.Synchronize() operation is taking about 2.5 minutes, and pushing my processor to 100%. Is this ok? Should it take this long to "realize" that it has no changes to sync?

     

    Thanks,

    Estanislao

    Sunday, April 25, 2010 6:30 PM

Answers

  • Glad to see Im not the only one that is experiencing performance problems with syncing with mobile devices.  My performance tests actually ran slower then yours believe it or not!

    I actually went as far as opening a case with Microsoft with this one and they traced it to a bug with SQL Server Compact that could not easily be corrected.  If you take a look at your trace logs (the server trace I believe) you will notice that the delete query is the one where the bottleneck is occurring.  Our fix was to query both the server and client tables for changes and ONLY sync those tables.  If youre interested in any sample code let me know.

    You are correct with the sync group concept, Microsoft as well as I ran tests and realized that a single sync group will perform better then multiple.

    The SQL Server Compact team was going to originally issue a QFE on the issue but then realized that it was a much bigger issue then originally thought and did not feel comfortable with creating the release.

    Monday, May 3, 2010 2:42 PM

All replies

  • do you have batching enabled? how many sync groups have you defined for the 50 tables?

    try enabling WCF Tracing and see how big the message is for 50 tables. I'm guessing the issue maybe around checking changes for each of the 50 tables or serializing/deserializing the synccontext dataset via WCF.

    Tuesday, April 27, 2010 12:48 AM
    Moderator
  • No, i didnt enable batching yet.

    About the groups, if i define one group for each table, i get around 5 minutes of syncing, if i define only one for all tables, i get around 3 minutes.

    I already traced WCF, and kinda discarded that the problem was the channel. Hardly any data transfered. I am concerned about the device 100% CPU usage i get though. Is verifying the changes so costly that it tops the device CPU during almost all of the syncing time?

     

    Tuesday, April 27, 2010 7:54 PM
  • its querying 50 tables one by one for inserts/updates/deletes  (not to mention checking the metadata for last sent/received for each table), so it may be a bit busy.

    have you tried applying this hotfix? http://support.microsoft.com/kb/973058 Not sure if it's entirely related but worth a try.

     

    Tuesday, April 27, 2010 11:12 PM
    Moderator
  • Yes, I already tried the fix, thanks.

    With the SyncTracer client side, I was able to verify that the queries to the SQL Server CE database asking for changes were the CPU bottleneck, so I had  to move the synching to the server.

    Thx anyways

    Friday, April 30, 2010 10:42 PM
  • can we drill this down a bit more ?

    Querying for changes on the SQLCE Db takes a lot CPU makes a sense, is this also the perf bottleneck as well in your case ? I would suspect what you are experiencing is an issue that querying metadate during sync for data application or enumerations. can you provide the details of your findings in the sync trace ? e.g. where the most of the time were spent in your case ?

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, May 3, 2010 9:08 AM
    Moderator
  • Glad to see Im not the only one that is experiencing performance problems with syncing with mobile devices.  My performance tests actually ran slower then yours believe it or not!

    I actually went as far as opening a case with Microsoft with this one and they traced it to a bug with SQL Server Compact that could not easily be corrected.  If you take a look at your trace logs (the server trace I believe) you will notice that the delete query is the one where the bottleneck is occurring.  Our fix was to query both the server and client tables for changes and ONLY sync those tables.  If youre interested in any sample code let me know.

    You are correct with the sync group concept, Microsoft as well as I ran tests and realized that a single sync group will perform better then multiple.

    The SQL Server Compact team was going to originally issue a QFE on the issue but then realized that it was a much bigger issue then originally thought and did not feel comfortable with creating the release.

    Monday, May 3, 2010 2:42 PM
  • In my particular case, what I noticed from the client-side log (device log) was the following:

    Enumerating Inserts: ~1 sec/table

    Enumerating Upadates: ~0.7 sec/table

    Enumerating Deletes: ~1.3 sec/table

    - In all cases, changes enumerated: 0

    End Enumerating Changes -> Start Applying changes: ~12 seconds

    Applying Deletes: ~1.3 sec/table

    - In all cases, Deletes applied: 0

     

    Its interesting to notice that only Deletes where tried to be applied. Maybe it has something to do with the bug Andrew mentioned?

    Im definitely intrested in some sample code for querying client tables for changes, thx.

     

    • Edited by Estanislao Tuesday, May 4, 2010 1:20 AM readability
    Tuesday, May 4, 2010 1:17 AM
  • I am glad to see Andrew pitching in here and sharing out his issue and fixes. We will need them.

    in Estanislao's case, it seems Applying Deletes took relatively smaller time than the issue Andrew mentioned ( i would believe it will be in 5+ seconds ). it seems most of the time ( ~12 seconds ) was spent "after client enumerated local changes and start applying changes from the server" -- which indicates transport time and the time server spent to applying the changes from clients. it would be helpful we enable the server side trace and also the WCF trace to get details of it.

    BTW, we are putting together some blogs and mini whitepaper on dealing with syncing with large number of tables on devices and hopefuly it can provide some general guidelines of improving perf on devices.

    thanks

    yunwen

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, May 4, 2010 7:55 AM
    Moderator
  • Estanislao,

    What would be the easiest way to upload the sample code to you?  I could not find an upload option here.

    Thanks

    Tuesday, May 4, 2010 2:55 PM
  • Yunwen, I think you missed the "sec/table" part. That log was generated by a database with multiple bidirectional tables. Enumerating changes took a total of 1min 10sec , and applying changes took a total of 1min 12 sec. As you can see, the ~12 seconds in transport is not the bottleneck.

    Andrew, could you email them to me at my hotmail account "estanislaotisco"?

    thanks,

    Estanislao

    Wednesday, May 5, 2010 2:05 PM
  • Makes a sense and in this case, skipping the empy tables for sync will be a reasonable and relatively easy workaround.

     

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, May 6, 2010 8:24 AM
    Moderator
  • I have emailed that off to you.
    Thursday, May 6, 2010 6:00 PM
  • Hi Andrew!

    Could you please send me the sample code as well, i guess I'm experiencing same problems.

    thank you

    Sunday, May 23, 2010 11:09 AM
  • Hi Andrew,

    sorry for bothering you - but it would be great if you could share your code with us. We have the same problems here and an efficient way to check which local tables have changes so to only sync those sounds very promising.

    As I guess it is too long to copy in here, perhaps you can upload it somewhere?

    Regards,

    Andreas

    Monday, June 7, 2010 12:24 PM
  • Could you provide me with an address I could forward it to?
    Tuesday, June 8, 2010 3:52 PM
  • Could you provide me an address I could forward it to>?
    Tuesday, June 8, 2010 3:53 PM
  • Andrew,

    try http://notepad.cc/ you can just paste in any text and it will generate a url for the pasted text.  I'm very interested in seeing what you have done and I am about do embark on the same work you have described here.

     

    thanks

    -drew

    Friday, June 25, 2010 4:52 AM
  • Hi Andrew!

    I would still be interested in your code.

    There is a new thread about this issue here.

    Is this the same solution you came up with?

    Regards,

    Andreas

    • Edited by forstingera Thursday, March 3, 2011 11:25 AM new Thread on this topic
    Tuesday, February 1, 2011 10:12 AM
  • I apologize for the delayed response.  That would be the solution that I implemented.
    Monday, November 7, 2011 9:51 PM