FIX: Initial synchronization of a replication to SQL Server Compact 3.5 subscribers takes significant time to finish

You create a subscription in Microsoft SQL Server Compact 3.5. If the size of the publication snapshot is large, the initial synchronization takes significant time to finish. This problem does not occur in Microsoft SQL Server CE 2.0.

Note This problem also occurs in Microsoft SQL Server 2005 Compact Edition.

Note This workaround also applies to SQL Server 2005 Compact Edition.

You can use some of the following methods to reduce the time to finish initial synchronization if the size of the publication snapshot is large.

Note Individual methods may not completely resolve the problem.

• Remove unnecessary indexes from the articles in the publication
  You can remove especially unnecessary indexes on columns of the VARCHAR data type. This method may not noticeably reduce the time for initial synchronization.
• Disable UpdateStatistics operations
  The Client Agent log shows that initial synchronization consumes significant time to perform UpdateStatistics operations. To disable the UpdateStatistics operations, follow these steps:
  1. Apply hotfix 963060.
     
     For more information, click the following article number to view the article in the Microsoft Knowledge Base:
     963060  (http://support.microsoft.com/kb/963060/ ) FIX: An error message is logged, and the synchronization may take a long time to finish when you synchronize a merge replication that contains a SQL Server Compact 3.5 subscriber: "UpdateStatistics Start app=<UserAppName>.exe"
  2. Use the new API that is introduced in hotfix 963060 to disable UpdateStatistics operations.
     
     For more information about how to disable UpdateStatistics operations, visit the following Microsoft blog site:
     
     http://blogs.msdn.com/sqlblog/archive/2009/04/15/sql-compact-performance-postsynccleanup-to-turn-off-updatestatistics-during-initial-download-of-sql-compact-replication.aspx (http://blogs.msdn.com/sqlblog/archive/2009/04/15/sql-compact-performance-postsynccleanup-to-turn-off-updatestatistics-during-initial-download-of-sql-compact-replication.aspx)
• Disable logs that are not needed on devices
  For example, you can disable the Client Agent log, or you can disable other kinds of device logs.
• Create the .sdf database file on the desktop computer first, use initial synchronization to populate the database file, and then copy the database file to the device
  This method relates to the topic of managing and deploying SQL Server Compact solutions to multiple devices. After you copy the database file to the device, you must synchronize the subscriber before you can use the database on the device. You can use this method for solutions of SQL Server 2005 Compact Edition and of later versions.
  
  For more information, visit the following Microsoft blog site:
  
  
  http://blogs.msdn.com/sqlservercompact/archive/2007/06/26/merge-subscribe-once-copy-and-deploy-multiple-times.aspx (http://blogs.msdn.com/sqlservercompact/archive/2007/06/26/merge-subscribe-once-copy-and-deploy-multiple-times.aspx)
• Increase the size of the buffer pool on the device
  By default, the size of the buffer pool on devices is 640 KB. You can change the size of the buffer pool by specifying a value for the Max Buffer Size option in the connect string. The following example sets the size of the buffer pool on devices to 4 MB.
  repl.subscriberconnectionstring = "provider=MICROSOFT.SQLServer.OLEDB.CE.3.5;data source=35Repl.sdf;SSCE:Max Buffer Size=4096";
  Notice that the Max Buffer Size option consumes more program memory on devices. If your device has more than 20 MB of free program memory, you will not experience a memory shortage after you increase the size of the buffer pool. If the data for initial synchronization is large, you can set the size of buffer pool to a reasonably bigger value only for initial synchronization.
  
  Note On some devices, you cannot increase the size of the buffer pool over 2 or 3 MB. In this case, this method does not apply.
• Move to more powerful devices
  New devices that you use to run SQL Server Compact 3.5 must satisfy the following:
  • You can use a larger buffer pool.
  • You can put the .sdf database file in the main memory.

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing the problem described in this article. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next software update that contains this hotfix.

If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site: Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Prerequisites

You must have SQL Server Compact 3.5 Service Pack 1 installed on your device before you install this hotfix.

Restart requirement

You do not have to restart the computer after you apply this hotfix.

Hotfix replacement information

This hotfix does not replace a previously released hotfix.

File information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

Windows CE 4.0-based operating systems on ARMV4-based Pocket PC devices

Windows CE 5.0-based operating systems on ARMV4i-based Pocket PC devices

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

For more information about the Incremental Servicing Model, click the following article number to view the article in the Microsoft Knowledge Base: For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base: For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base: