SQL Compact Performance: PostSyncCleanup to turn off UpdateStatistics during initial download of SQL Compact Replication

  • Article

SQL Compact Replication initial subscription may take significant time on devices. The UpdateStatistics operation adds additional overhead to the initial download.

Indicators:

The Client Agent log may show the start and end times of UpdateStatistics operation. For example:

SQLCECA: 04/09/2009-12:21:26 UpdateStatistics Start app=<appName>.exe

SQLCECA: 04/09/2009-12:27:59 UpdateStatistics End hr=0 app=<appName>.exe

Workaround:

Recent hotfixes for SQL Compact 3.5 and 3.1 (KB article URLs given below) expose a new API for SQL Compact applications to turn off UpdateStatistics during initial download of Replication.

Disabling UpdateStatistics can be done only on the merge replication, and not for RDA operations. One should use,

SqlCeReplication repl = new SqlCeReplication();

…

repl.PostSyncCleanup = 2;

to make sure that update stats is not called after merge replication sync.

To use the new method in your SQL Compact app, in addition to applying the hotfix to device, you will also need to deploy the updated System.Data.SqlServerCe.dll to the desktop folder where VS can find it, else an error will occur:

'PostSyncCleanup' is not a member of 'System.Data.SqlServerCe.SqlCeReplication'.

You can follow these steps to resolve the above error:

 

Extract SYSTEM~1.004 file from the device cab, rename it to “ System.Data.SqlServerCe.dll ” and replace at the appropriate path.

When working with SQL Server Compact 3.5 (Visual Studio 2008):

“C:Program FilesMicrosoft SQL Server Compact Editionv3.5DevicesSystem.Data.SqlServerCe.dll”

If your project refers to “System.Data.SqlServerCe.dll” in another location then replace the DLL at that location as well.

Once the modified app runs on the device, if the hotfix bits were not deployed you may see the following error (as expected):

“Method not found: set_ PostSyncCleanup

System.Data.SqlServerCe.SqlCeReplication”

Uninstalling SQL Compact 3.5 SP1 RTM and installing the hotfix .cab files should resolve the above error.

References:

https://support.microsoft.com/kb/963060

https://support.microsoft.com/kb/960142

Posted by: Syed