Performance: Tips for faster download of a large SQL Compact 3.5 database via Replication
SQL Compact 3.x Replication initial subscription is resource-intensive and may take significant time on devices. Read the introduction to the SQL Compact Blog for this problem.
Introduction ( fromSQL Compact Blog )
“The initial subscription is going to bring down lot of data from server to client in merge replication. It means that the requirement for bandwidth, memory, processing power is going to be high for the creation of subscription. However, for devices all these requirements are not easy and even if they are available, it takes very long time to get the subscription created on the client database.”
Symptoms:
SQL Compact 3.5 Replication initial download for certain large publication takes a significant amount of time on mobile devices. The corresponding publication with earlier versions (SQL CE 2.0 and SQL 2000) did not take that much time.
Performance Tips:
Here is a list of workarounds which have a potential to reduce the download time for large initial sync. Please keep in mind that choosing a single option may not resolve the issue completely. However, a combination of several options may bring the download time to an acceptable level.
- Remove unnecessary indexes from the server side (articles), especially indexes on Varchar columns
This helps to a certain extent; however the overall download time may still be high.
- Apply hotfix to turn off UpdateStatistics:
The Client Agent log during download indicates that a portion of time was spent in executing update statistics. After applying a new hotfix release, the application may invoke a new method call to turn off update statistics.
PostSyncCleanup to turn off UpdateStatistics during initial download of SQL Compact Replication
KB https://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
- Turn off unnecessary logging (Client Agent Log, other types of device logging) on production devices.
- Move to a high-end device, where one can use higher buffer pool sizes and can also place the database .sdf file in main memory as appropriate.
- Pre-create the .sdf on desktop and then download to devices.
This relates to managing and deploying the solution to n number of devices in the field. You may use the move-copy detection feature of SQL Compact on devices. For more details on how it works, please see the SQL Compact team’s blog entry “"Merge Subscribe Once, Copy and Deploy Multiple Times" at https://blogs.msdn.com/sqlservercompact/archive/2007/06/26/merge-subscribe-once-copy-and-deploy-multiple-times.aspx
- Increase buffer pool size:
We got some real good benefit in terms of running times by increasing buffer pool size to higher values. By default this value is 640K on a device. The running times for a very large single-table publication with Max Buffer Size of 640K, 1024K, 2048K, and 4096K were as follows:
1) 640K - 86 m 34 secs
2) 1024K - 73 m 56 secs
3) 2048K - 55 m 13 secs
4) 4096K - 38 m 30 secs
You can specify ‘Max Buffer Size’ in the connect string, as follows:
repl.subscriberconnectionstring = "provider=MICROSOFT.SQLServer.OLEDB.CE.3.5;data source=35Repl.sdf;SSCE:Max Buffer Size=4096";
Keep in mind that this setting consumes more program memory on the device. However, this should not be an issue if you have a good 20 MB of program memory available. If the initial sync data is huge, you may set the buffer pool size to a conveniently higher value only for initial sync.
There are real improvements in running times with bigger buffer pool sizes. However, certain devices can't increase buffer pool size beyond 2-3 MB, so, a very large buffer-size is not always applicable.
Fix for rowguid index processing delays:
In some cases, the delay is due to rowguid index processing, please look for the hotfix KB 970414 when it is available.
Comments
- Anonymous
May 09, 2015
Nice