FAQ: Why is opening my SQL Server Compact database slow?

You may experience, that opening a connection to a SQL Server Compact database file using SqlCeConnection.Open() (or doing this via EntityFramework or LINQ to SQL) can be unexpectedly slow (up to 30 seconds, for example). In this blog post I will detail the known reasons for this to occur, and outline solutions and workarounds.

1: The database has been created on another platform

One of the most common issues is that the database file has been created and populated on another platform than the one where the app is running .Open(). For example: a database file is included with the app, a database file is downloaded from a server or similar. Different platforms are for example Windows 8 versus Windows XP, and Windows 7 versus Windows Mobile. Notice, that Windows XP, Server 2003 and Windows Mobile are compatible platforms. And Windows 8 and Windows Phone appear t be compatible in most cases also. The product team did a blog post about the issue many years ago, but the issue is otherwise poorly documented, and therefore pops up again and again. There is no solution to the issue, only workarounds.

Workarounds:
Create the database file on the platform after app installation, using for example my scripting APIs.
Include a database file per target platform, that is already built/opened on the target platform (in particular an issue for read-only databases, see my blog post here)
– Avoid indexes with string based keys (probably not possible in most cases)

2: The ACL (Access Control List) on the RSA folder is corrupt

This issue appears to happen only on Windows XP system and only with encrypted databases. The ACL on the C:Documents and SettingsAll UsersApplication DataMicrosoftCryptoRSAMachineKeys folder are corrupted, and users are unable to access file in this central folder.

Solution: Reset the ACL to allows all users Read access, either using the UI or via the xcacls command line tool.

3: Invalid Internet Proxy configuration

This appears to occur for SQL Server Compact 4.0 on any system with an invalid proxy configuration, as a Certificate Revocation List check is run each time the engine is loaded (which happens on the first call to .Open()).

Solution: To avoid this delay, which probably affects any signed app on the system in question, you must fix the configuration or disable the check. The check can be disabled via UI or via registry settings, as described here.

Hope this helps you troubleshooting slow database opening with SQL Server Compact.

Tuning the Performance of Backup Compression in SQL Server

Here is a great read on Tuning the Performance of Backup Compression in SQL Server

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx

And a cheeky query to help give you a guide on the size of your backup if it was compressed !


SELECT
b.database_name ‘Database Name’,
CONVERT (BIGINT, b.backup_size / 1048576 ) ‘UnCompressed Backup Size (MB)’,
CONVERT (BIGINT, b.compressed_backup_size / 1048576 ) ‘Compressed Backup Size (MB)’,
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) /
CONVERT (FLOAT, b.compressed_backup_size))) ‘Compression Ratio’,
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) ‘Backup Elapsed Time (sec)’
FROM
msdb.dbo.backupset b
WHERE
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0
AND b.backup_size > 0
ORDER BY
b.backup_finish_date DESC


Table 1 shows the output of the above query after taking an uncompressed and a compressed backup.

Database Name Uncompressed Backup Size (MB) Compressed Backup Size (MB) Compression Ratio Backup Time (Seconds) Comments
BCTEST 292705 95907 3.05 1705 Compressed backup
BCTEST 292705 292705 1 3348 Uncompressed backup