MS Sync framework "Enumerating Inserts for Table xxxx" is suddenly extremely slow

Question

Edited with new info.

I inherited a project that utilizes MS Sync Framework 2.1 to sync a SQL Server CE 3.5 DB with SQL Server 2008 R2. There are about 1 million rows spread over a dozen tables. Two of those tables account for ~75% of the total rows. Only a few dozen rows change per day, and there are less than 10 users / installations in this spoke-and-hub arrangement. Sync has very little work to do each day.

The previous version of this project could complete a "do-nothing" sync (zero rows inserted, updated, or deleted on either end) in 1-2 minutes. Since I re-generated the .sdf (including all data), it is now taking 9 minutes minimum, and far longer for some users.

By enabling verbose sync logging, and comparing new log files to older ones, I narrowed the problem down to one type of operation. All the extra time is accounted for in the steps logged as "Enumerating Inserts for Table xxxxx" (client-side inserts waiting to go to server). Bigger tables take longer than smaller tables, but all of them have increased proportionally, and dramatically, for this one operation, even if the result is zero rows.

Edit: what appeared to be unsupported query syntax (for SQL Server CE) appearing in the sync log, was apparently only invalid in combination with the server explorer query window. It works fine in the VS T-SQL editor. I have now compared the two actual query plans. They are the same. In one sense it's no surprise that the large table is being scanned. However, in the T-SQL editor it takes the same time on either DB (~35 seconds). Apparently much more is going on in the sync engine during that operation; even though the result is zero rows in all cases.

When I inherited the project it was about half way through a minor upgrade, but I do not see anything that stands out in the source control history. There is no documentation that exactly describes the dev-workstation setup from before. I have compared all the tables, columns, and indexes to the older .sdf. The new .sdf and older .sdf are the same size. There are no newer libraries that I'm aware of. I am thoroughly stumped.

What am I overlooking?

Answer 1

Although I never learned why the newer file performed slower than the old when enumerating local changes to be uploaded, I figured out how to make it perform like it should (wicked fast) in this scenario.

As noted before, the framework doesn't create any indexes when generating the cache. A little (actually a lot of) experimentation revealed that creating this one index (on every table) appears optimal:
create index idx_ChangeTracking on yourTable (__sysChangeTxBsn, __sysTrackingContext, __sysInsertTxBsn);

Enumerating local changes is now virtually instant, as it should have been all along. Why is this index not created by the framework when generating the tables, and why didn't it matter (as much) previously? I guess we'll never know.