none
Synchronization performance degradation over time RRS feed

  • Question

  • I'm hoping the sync guru's among us can help with the problem we're experiencing using the MS Sync platform to synchronize between SQL Server and occasionally connected windows mobile devices.

    We've developed a windows mobile application that has been running on WM5.0 devices since 2009. It uses the sync framework to synchronize data between the device's local SQLCE3.5 database and the consolidated SQL Server 2008 server db. The application works well, and it's matured to a point where there are no crashes and generally no forced restart situations (excepting the occasional situation where the battery dies or there is some kind of hardware failure).

    However: what we're experiencing now is a dramatic increase in the time required to upload data from the device. If we kit a device with our app "out-of-the-box", and start with a fresh, empty database, an upload session takes between 4 and 6 minutes. While I wish it were faster, we understand the limitations of the way ms sync framework is currently built and the time is acceptable. Generally, a user performs an upload each day or at least 5 times a week. What we're finding is that this upload time stays relatively constant for approximately 3-4 months. After this, the upload synchronization time begins to grow. It spikes upwards dramatically until it takes upwards of 45 minutes on some units.

    Does anybody have any idea why this might be happening? I know that's an open question, so here are some parameters:

    1.  the amount of data being synchronized is not growing. The user is essentially uploading invoices/orders that he captured during the day. He certainly isn't suddenly capturing 10 times as much information as he was when he started.
    2. The amount of data being stored on the client is not growing. We use a "rolling window" cutoff for storing information, so any captured data that is time sensitive is being deleted on the client after it's aged 8 weeks. So, once a client database is 8 weeks old it's approximately as large as it's going to get.
    3. the download portion of the sync does not degrade performance wise over time. Our database is structured so that most of the tables are synchronized one-way. Data flowing INTO the device is fine. It's the upload side.
    4. The problem isn't on the server. It's not taking longer to perform inserts/updates on the server for data received from the client.
    5. If we reset a client database and re-initialize it from the server the issue is resolved for that client (at least for another few months).

    Simply put: The client is taking longer to determine which rows have changed and are eligible for synchronization. I'm no expert on exactly how the change-tracking metadata is structured inside an SQLCE db, but it appears that somehow the queries being executed to determine what to sync are slowing down over time.

    Does anyone have any ideas on where I can start looking to determine the issue?

    Thanks in advance,

    Paul

     

    Friday, October 21, 2011 6:04 PM