Microsoft Sync Framework query performance

Question

We're developing a product that relies on the Microsoft Sync Framework to keep the data on a client app and on the server in sync. What we have noticed is when syncing about 16 tables and ~2200 records it will take about 4 minutes, which is not acceptable.

Using the SQL Server Profiler we found it is using sp_executesql to execute the queries. when run without sp_executesql a specific query runs in <1s, but with it takes over 10s.

So the question is: What are we doing wrong and is there anything we can do to speed it up.

Answer 1

sp_executesql is using dynamic sql. each row is being processed singularly.

4 minutes seems rather long, you should look at the design of your 16 tables and check the performance on them. Try using the execution plan to view where the bottleneck may be occurring.

check out this link that covers the basics:

SQL Execution Plan basics

Try scrolling down to the bottom of the article, where there are screen shots of the actual graphical interface in SQL Management Studio. There are some boring parts of the article, but you can atleast see the graphical execution plan and its benefits.

Answer 2

Performance improvements are listed on MSDN in the 'What's New' section for Sync Framework 2.0.

Performance Improvements

The new database providers in this release have been thoroughly tested in large scale out scenarios in which a single server supports thousands of clients with hundreds of concurrent synchronization operations. This testing resulted in a large number of internal performance improvements enable Sync Framework database providers to perform as well as other Microsoft technologies like Remote Data Access (RDA) while offering a wide range of capabilities that compete with end-to-end solutions like merge replication.