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 Files\Microsoft SQL Server Compact Edition\v3.5\Devices\System.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:

 

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

 

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

 

Posted by:  Syed