locked
Slow Slow Synch Services RRS feed

  • Question

  • We are hoping a guru on the forum can point us in a direction on challanges in implementing Synch Services.  After move than 18 month's of releases we are hoping Sync Services is ready for prime time in a synch to a Pocket PC.  Here are the issues and workarounds we've seen.

     

    Our use of Sync Services is to replace RDA Sync to a PPC.  RDA will synch about 5,000 rows to a new 6 MB SQL CE database on a Pocket PC in about 70 secs on a fast link.  "Out of the box" Sync Services needs nearly 5 minutes to synch 1/2 the data. 

     

    Using a test app to log the stages of sync shows the time difference is between the time the dataset is received at the PPC and the completion of the data inserts.  Since the dataset is used to send the inserts to the PPC, there must be some inefficiency in the dataset inserts to the SQL CE database.  Is there some way to speed this up or would we have to use put extra code in the sync process to use a more efficient bulk insert approach ?  Has anyone done this ?

     

    Here are a few wish list items of issues we've seen in Synch Services to a PDA:

     

    - Dataset serialization/deserialization memory spikes on a Pocket PC are addressed with batching only at the cost of longer sync times.  A post here mentions this is a priority on the next release.

     

    - Dataset databloat adds to wireless costs and sync times.  Could compression and binary serialization as the standard fix this ? 

     

    - Sync Adapter commands seem to work in a their own sandbox.  Syncing joined tables end up executing the same SQL again and again.  Temp tables is not a great answer.

     

    Thanks to all in looking at this.

     

    • Moved by Hengzhe Li Friday, April 22, 2011 3:07 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, January 2, 2009 3:59 PM

Answers

  • PPCDude-

    The method currently used by the SqlCeClientSyncProvider is using SqlCeCommand statements.  Currently there is no way to override this behavior, however we are investigating how to improve performance in future releases and are aware of some optimizations that can be made.
    Friday, January 9, 2009 12:18 AM

All replies

  • We came across a reference that may be the reason behind the problem.  Steve Lasker has a comparison of insert speeds into SQL CE.  It appears that the performance for a new connection each time is 6 inserts per second.  The speed with a SQLCEResult set is over 10,000 per second. 

     

    Could a Synch Service person say what the method of new inserts used by the SqlCeClientSynchProvider is ? 

     

    Is an override of the ApplyChanges method of the SqlCeClientSynchProvider to use SQLCEResult Sets an answer ? 

    Friday, January 2, 2009 10:31 PM
  • PPCDude-

    The method currently used by the SqlCeClientSyncProvider is using SqlCeCommand statements.  Currently there is no way to override this behavior, however we are investigating how to improve performance in future releases and are aware of some optimizations that can be made.
    Friday, January 9, 2009 12:18 AM
  • Thanks for the info on SqlCeCommands in the SqlCeClientSyncProvider.  Using SqlCeResultSet Inserts was 10x plus faster.  We are struggling with Device Sync Services, but feel it could work for us.  Could you comment on the issues below ?  Sorry for  the article length post, but there is that much detail.

     

    A Sync of moderate SDF sizes of 6MB on a PC took 6 seconds.  The same synch to a PPC required over an hour using a local PPC so it wasn’t a slow data link.  Sync Services appears  aimed at PC’s and doesn’t work to a Pocket PC without major tweaking.   The hooks and events appear there to do the tweaking.  The hope is that the tweaks don’t undo something key in Sync.  That’ll put us back to the ‘rolling our own’ approach which we’ve seen used by other software company’s.

     

    Here are 7 optimization areas we’ve identified.  Could a knowledgable MS type comment on any issues here ?  We aren’t able to wait for the next release as there appears to be an RDA bug that occurs in WM5 and WM6, but not PPC2003SE.

     

    SqlCeResultSet Inserts.  SqlCeResultSet inserts put in the ApplyingInsert Event of the SqlCeClientSyncProvider had a 10x plus improvement in run speed.  The Insert rows in the DataSet were deleted.  Would this mess anything else up with Sync ? 

     

    DataSet Serialization.  There are lots of blogs of out of memory due to this.  The small memory, even on an empty Pocket PC means the only answer is lots of batches.  Blogs mention 10,000 rows as the point to need batching.  Our rows must be big as anything over 1,000 rows gives out of memory.  Beyond memory, this is very very slow.  The DataSetSurrogate class seems the answer this.  The example, even reference in sync guy Mayjayar’s blog is for Remoting.

     

    http://blogs.msdn.com/mahjayar/archive/2008/10/01/dbsyncprovider-improving-memory-performance-in-wcf-based-synchronization.aspx

    http://support.microsoft.com/default.aspx/kb/829740

     

    BinaryFormating.  An article by Scotto

     

    http://www.freewebs.com/compactFormatter/About.html

     

    Seems to have a LGNU formatter in managed code faster than the native BinaryFormater.  A native, fast formatter on the PC and the PPC end would be great.  Does the WCF formatter do this ?

     

    Compression. This has several blog entries also.  An article:

     

    http://www.eggheadcafe.com/articles/20040311.asp

     

    Uses the above 3 techniques to report 50x improvements in synch.  The article is years old and managed codes compression.  Does the GZIP support on the PPC replace the managed compression Bromberg has ?   Could his compression be faster than the MS GZip ?

     

    Batching.  Batching is listed as an answer to DataSet Serialization out of memory issue.  Batching is spoken of as a faster way to synch.  Tests seem to show it is actually much slower, but does avoid the out of memory.  Perhaps use of all the other techniques here will eliminate need for batching.

     

    SDF Download.  The initial sync has to have the most data and be the biggest challenge.  In the tests above, the sync on a PC to make a 6 MB SDF took 6 seconds.  This zipped to 2 MB.  Passing the SDF avoids lots of steps.  The PPC will have less CPU, memory etc than the server.  An option to do the initial sync this way could answer lots.

     

    PPC Memory.  In a PPC world of $5, 2GB SD cards, could the memory use be redirected there ?  PPC users are putting more and more on the devices.  All but the absolutely needed sys .DLL’s need to be on the SD card.  Does the temp storage setting in the DB Connect string also apply to Sync Services ?

     

    In a ‘Roll Your Own’ Sync, a very tight Sync Package could be developed.  Native serializers and native DB actions could be used.   Is this daydreaming ?

     

    Thanks lots for reading all this.  It’d be great if you could open the door a bit on the optimizations you are considering to help out some now.

     

    Friday, January 9, 2009 2:35 PM
  • Hello PPCDude,

     

    just wanted to thank you for investigating on this and sharing your ideas with us.

    We also run in the slow sync problems here - which really is a problem in terms of user acceptance of the system.

     

    I really hope some MS official will give us a more detailed reply here. It would be good to know what exactly the prob is and if there are any of the workarounds (perhaps some of those you propagated) that should help. Perhaps someone could then work on a custom SyncProvider that could at least improve performance for the meanwhile.

     

    If the performance problems are not solved within the next two or three months (at latest) we would be forced to switch to another sync mechanism - which I would really hate so to speak.

     

    Regards,

     

    Andreas

    Monday, January 12, 2009 10:53 AM
  • The principal problem in the SqlCeClientSyncProvider for me, is the way they use SqlCeConnection

     

    There is no Pooling in Sql Ce, so the Open() and Close() method on SqlCe is just something terrible for performances.


    Check the provider in Reflector. They use SqlCeConnection like we use SqlConnection on Sql Server 2008 with Pooling.

    It's a terrible mistake in my own opinion. There is a open and a close on all synctables. It's just .... terrible !

     

    I think a new SqlCeClientSyncProvider with a custom pool SqlCeConnection will increase the performance in great propotions...

     

    I have thought to develop a custom SqlCeClientSyncProvider, but we cant ! Because we dont have rights on Change Tracking on Sql Ce by code. Can't activate or deactivate it . (humm... why ?)

     

    My Two Cents (sorry,  I m french, so it's "my 2 Euros" )

    Tuesday, January 13, 2009 10:11 PM
  • Good feedback on options to improve perf around the devices scenarios guys.  Most of these we are aware of and some even have in-house prototypes.  We will post something on our blog when these improvements have been released.   Because Sync Services for devices provides a significant amount of functionality above and beyond that which is offered by RDA, we made the conscious decision to release these bits regardless of the perf variances.  We recognize that the performance is unacceptable and are making good progress internally to address these issues. 

     

    I will make an attempt at talking to some of the feedback provided above.  For those that are seeing memory issues, batching is a great option but is limited to download only.  This has been addressed in our next release insomuch that we now support bi-directional batching.  As someone pointed out above, you do take a hit to overall sync time but for many, this is the lesser of two evils insomuch that OOM is not an option.  In our latest tests we have found that batching adds about 10% variance. 

     

    I would like to understand more about the comment related to joined tables resulting in superfluous commands being executed.  Could someone provide some more detail as I was not aware of this issue?

     

    SQLCEResultSet is an excellent way to tune the runtime bits and we already have a prototype in-house.  We are also aware of the improvements that can be realized through a more intelligent use of CE connection objects.  These improvements are a no brainer and will be our first line of attack.  Unfortunately, this does not improve the update and deletes.  It would be good to hear some feedback around the composition of your DML operations i.e. %inserts vs %updates vs %deletes coming down to the device.

     

    I cannot comment on the effect of "tweaking" without knowing what specifically you are referring to.  Could someone add some detail here?

     

    DataSetSurrogate is something we are aware of.  Mahesh is one of our new and in addition to being a great blogger, he is an incredible asset to the team given his expertise around WCF.  Duly noted on this feedback and we will certainly look at this as well as a part of our overall perf improvement strategy.

     

    Regarding binary formatting, we are aware of some of the limitations around perf related to standard formatting.  Again, we are looking at this as an option to improve perf.  There are questions around the ROI we might get regarding sync time given the churn that may be required.  These questions have yet to be answered and we just need to do the due diligence around benchmark comparisons.  Compression is a similar story insomuch that we have quite a few options and need to perform some benchmarking before we recommend anything.

     

    SDF download is a great approach and one that quite a few companies have taken.  One of the things that we would like to do is put together some samples and post these to our blog.

     

    I am not familiar with the PPC setting mentioned but will take it as an action item to look into this further as another option for improving perf.

     

    Again, we apologize for the shortcoming in our devices scenarios and we recognize that this needs to be addressed.  Thank you for your feedback.

     

    Regards,

     

    Sean Kelley

    Program Manager

    Microsoft

     

     

     

    Wednesday, January 14, 2009 7:08 PM
    Moderator
  • Sean,

     

    Your response was a really great.  Rather than putting the optimizations into the core synch product, you might just publish some of the prototypes you mention as examples.  Several of the optimizations are mentioned by people on your team.  Without examples, there's lots more time to implement them.  This could be a fast way to make Sync Services effective on devices.

     

    We have a bit more information from recent efforts:

     

    We have to still proceed on getting fast device sync's done inhouse due to customer needs.  We are getting through the list of optimizations using the work arounds listed in the prior post.  Each optimization has been worth the effort.  I've seen postings where some of these optimizations were noted as not worth it.

     

    One optimization in particular that we hadn't seen on any postings or blogs has been very effective.  The greatest challange in device sync is on the initial synch, especially if an end user wants to reinitialize their synch over a wireless connection.  Creating the SDF on the server is way faster than on the PDA due to computing resources.  For our typical synch's this is by nearly a factor of 100.  ie. 1 minute to 100 minutes.

     

    Downloading a zipped version of the initial SDF really works well.  SDF's compact to about 40% of the original size with GZip.  Unfortunately, SDF encryption is not compressible by GZIP.  GZip is pretty low performance and a poorly designed class.  A normal zip goes to about 25% of the original size.  RAR zips are more yet.  This is key for slower network connections where the charge is by the MB.  Other managed zip open source solutions are possibly pretty good, but are quite a task to understand all their features.  A native zip intrinsic to the sync or WCF would be great.  Perhaps this exists within WCF and we haven't found it yet.

     

    Device synch's are very likely to involve partitioned synch's.  In most real world environments where a database is active, this means you can't send all deletes to every client.  The deletes that don't apply could amount to many times the actual size of the sync.  This requires some pretty complex triggers to keep up with changes in the fields used for the partitioning criteria.  You might consider an example/scenario on this.  Perhaps an adaptation of Peer to Peer would address this better than more complex triggers and more complex tomb_stone tables.

     

    Thanks again for your advice and candor.

    Wednesday, January 14, 2009 9:54 PM
  • Hello Sean,

     

    thank you so much for posting here and letting us feel you sync guys work on that issue!

     

    Just because you asked for feedback on insert : update : delete ratios - for our scenario here it would already help a lot if local inserts on the device would run fast(er).

     

    The scenario is like that we have users out in the field and we want to sync their "tasks" for that day (actually we wanted also future tasks but at the current sync performance even todays task are a horror).

    Of course the overall speed should improve (e.g. the required time per table to get the local changes - if that decreases we could sync more often to check if there where server chances) but if the inserts coming from the server would be applied locally faster it would already help us a lot in terms of user acceptance.

     

    Updates and deletes are not that important in our scenario because the default workflow is like task is created on the server, task is assigned to a user, user syncs and gets task including all required data (actually 4 tables involved, avg. 100-150 rows to insert - in total, not per table). After completion of all tasks sync and upload to server again. Of course there _could_ be updates to the task, but they are the exception.

     

    So it would be great if you could share with us some of the concepts you are trying in your prototypes or at least some tipps if implementing the inserts using SQLCEResultSet and handling them on my own in the SyncProvider will do the trick.

     

    Best regards,

     

    Andreas

    Monday, January 19, 2009 9:05 AM
  • Hello Andreas,

     

    The really slow inserts are greatly aided by replacing the Insert part of Sync Services with a SqlCeResultSet insert.  There is a blog by an MS person benchmarking SqlCeResultSet inserts as way faster than the SqlCeCommand used not in Sync Services.

     

    Simply put the insert in the ApplyingInsert event of the Sync provideer on the device as shown below.

     

    void lp_ApplyingChanges(object sender, ApplyingChangesEventArgs e){

           SqlCeClientSyncProvider lp = (SqlCeClientSyncProvider)sender;

           // DoStatus("Applying Changes "); // + lp.GetTableReceivedAnchor("Building").Anchor.ToString() + "/" +

           foreach (DataTable Tbl in e.Changes.Tables)

                using (SqlCeCommand cmd = new SqlCeCommand(Tbl.TableName,

                      (SqlCeConnection)e.Connection,(SqlCeTransaction)e.Transaction)){

                           cmd.CommandType = CommandType.TableDirect;

                          using(SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable)) {

                               for (int j=Tbl.Rows.Count-1; j >=0; j--)

                                   if (Tbl.Rows[j].RowState == DataRowState.Added) {

                                          SqlCeUpdatableRecord rec=rs.CreateRecord();

                                          Object[] vals = Tbl.Rows[j].ItemArray;

                                          for (int i=0; i < Tbl.Columns.Count; i++ ) recIdea= valsIdea;

                                          rs.Insert(rec);

                                          Tbl.Rows[j].Delete();

                                   }

                           }

                }

    }

     

    This code works as a prototype, but may have issues in that the delete from the dataset may cause problems with later Sync Services actions.  The speed increase from this is over 10x for our test scenario.

     

    The above is one of 8 changes mentioned in this thread to speed up Synch Services on devices.  Many of the changes to Synch Services to make devices work are not a lot of code.  It's time consuming to figure these fixes out and very difficult to know if the fixes don't create other problems as we can't see the source code of Synch Services.  

     

    The alternative of just "roll your own" on Synch and not using Synch Services is tempting.  Many of the areas of slowness could largely be fixed by a series of code snippets from MS.  Earlier in the thread, an MS person mentions several inhouse prototypes exist to address the areas of slowness.  Can't we peek at some of these prototypes ???  How bout a Blog entry ?  Bet MS would get lots of feedback on results to make the ultimate fix release on this even better.

     

     

     

    Monday, January 19, 2009 1:22 PM
  • May I ask Sean, if the improvements to crud operations are improved in the MSF, will they be made available as a service pack/update to v1.0 of the product, or is it likely to be something that will be kept for v2.0 only?

     

    Reason I ask, is that I read in another post the v2.0 will probably not be released until Q3 2009 at the earliest, so wondered if an update to v1.0 would appear sooner?

     

    Many thanks.

    Tuesday, January 20, 2009 11:54 AM
  • First of all, thanks to everyone on this thread for a fantastic post, particularly PPCDude.  I'm not trying to use Sync Framework on a mobile device, but I am trying to improve performance.

     

    I'm currently trying to implement the DataSetSurrogate solution within Sync Services for ADO.NET to reduce the amount of traffic on the wire, and I'm not sure how to proceed.  I'm hoping someone on this thread might be able to point me to a solution.

     

    Here's my full forum posting containing the question:

     

    http://forums.microsoft.com/sync/ShowPost.aspx?PostID=4310721&SiteID=75

     

    Thanks in advance to anyone willing to take a look at that and help me out!

     

    David Cater

    Wednesday, January 21, 2009 4:24 PM
  • David,

     

    The note in your post that you linked to above is worth repeating

     

    The blog entry above from Mahjayar has this quote in it regarding the DataSetSurrogate:

    "Download it and use it in your WCF based Synchronization apps and you should see vast improvement in your memory usage and performance. "

    That makes it sounds like it should be very simple to simply "use it", but so far I'm blocked. 

     

    A short post from Mahjayar would sure be helpful.  With that thought out there, here are some thoughts that may help you:

     

    The use of the DataSetSurrogate on a PC to Server scenario probably has to override the GetChanges and ApplyChanges methods on the SyncProviders.  Make a SyncContext2 class to pass over the wire:

     

    public class SyncContext2{

    DataSetSurrogate dss;

    public SyncContext sc;

    // this must be attributed to be used ??? See Service1 type sample

    public void CompressIt(){

    dss = new DataSetSurrogate(sc.DataSet);

    sc.DataSet.Clear();

    }

    public void UnCompressIt(){

    sc.DataSet = dss.ConvertToDataSet();

    }

    }

     

    On a PPC, it appears possible to put the conversion of a DataSetSurrogate back to a Dataset in the WCF proxy code for the prior 2 methods.

     

    We don't have this working.  It may be totally wrong.  If this works for you, would you let us know.  We'll do the same.  The class above could be cleaner, possibly as a descendent of SynchContext.

     

    It's really likely that an MS person (Mahjayar).  Could point us in the right direction on this.  Any chance of that happening in light of the posted example from Mahjayar applies to remoting ?

     

    PPCDude

     

    Wednesday, January 21, 2009 5:58 PM
  • Manipulating GetChanges appears to be a dead-end.  See the post I linked above for details.

     

    D.

    Wednesday, January 21, 2009 10:17 PM
  •  Hello,

    I just wanted to state that simply using the codesample from PPCDude increased our sync experience a lot!

    Thanks again for sharing and MS guys - please keep up the good work, we all can't wait to get our hands on the next CTP/release.

    Regards,
    Andreas
    Saturday, January 31, 2009 8:59 AM
  • I have been attempting the SDF Download perf recommendation above and not having much luck.  Everytime I move the database to the PPC, the initial sync process begins again.  I have tried the following methods to do this:

    - Created a PC Sync app that performs the exact same synchronisation as the PPC and then moved the SDF to the PPC, but the first sync still re-copies all data again.
    - Copied all data to a new SDF, added extra Sync columns for date created and date modified and populated them with a date in the current UTC datetime.  This still performs the initial sync again on the PPC.

    Can anyone who has successfully done this please fill me in on what needs to happen?

    Cheers.

    Sunday, February 1, 2009 5:14 AM
  •  Hi I'm using SqlCEResultset to initialize the database during synchronization process. By defautl SyncService add the values of 6 to this __SysChangeTxBsn column when i insert a new record and conside it as new record. Is there a way to update this tracking column or how to tell resultset not to update this column value and leave it as null
    Friday, February 20, 2009 6:08 PM
  • void lp_ApplyingChanges(object sender, ApplyingChangesEventArgs e){

     

     

           SqlCeClientSyncProvider lp = (SqlCeClientSyncProvider)sender;

           // DoStatus("Applying Changes "); // + lp.GetTableReceivedAnchor("Building").Anchor.ToString() + "/" +

     

     

           foreach (DataTable Tbl in e.Changes.Tables)

     

     

                using (SqlCeCommand cmd = new SqlCeCommand(Tbl.TableName,

                      (

     

    SqlCeConnection)e.Connection,(SqlCeTransaction)e.Transaction)){

                           cmd.CommandType =

     

    CommandType.TableDirect;

     

     

                          using(SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable)) {

     

     

                               for (int j=Tbl.Rows.Count-1; j >=0; j--)

     

     

                                   if (Tbl.Rows[j].RowState == DataRowState.Added) {

     

     

                                          SqlCeUpdatableRecord rec=rs.CreateRecord();

     

     

                                          Object[] vals = Tbl.Rows[j].ItemArray;

     

     

                                          for (int i=0; i < Tbl.Columns.Count; i++ ) recIdea= valsIdea;

                                          rs.Insert(rec);

                                          Tbl.Rows[j].Delete();

                                   }

                           }

                }

    }

     

    Hi PPCDude,

    I just tried your code below, but it does not work for me. I always get the Exception "Cannot change RoomId column" (RoomId is an int primary key in my table).
    How did you solve that? I guess you must have the same problem too or not?


    Regards,
    Martin
    Saturday, June 6, 2009 4:28 PM