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 Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys 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.
