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.
6 comments:
Hi. I've tried all the solutions you've described in this post but none of them fixed my problem (veeery long start-up time). Walking around I decided to change target platform for my project (EntityFramework 6.1.0 + SQL Server CE 4.0).
Application compilled using x64 platform runs very slow (in my case it is about 9-10 sec), using x86 much faster (in my case about 2-3 sec).
P.S. My system is Windows 8.1 x64.
Thank you for your work.
Thanks, Roman, would be great if you could provide a repro project, I Wonder if it is an EF issue
Thanks for the info.It helped me solve my issue.
In my case I synced SQLCE db from Win7 PC and had the sdf file sitting on a shared folder. Then from WinXP PC I opened and closed connection to the SQLCE db that resides on Win7 shared folder.
I noticed that the size of .sdf file increased. Now my handhelds copy the database from the win7 PC and start using them. To open a connection it now takes 2 seconds and compared to 18 minutes on a 248 mb sdf file.
Thanks
Thanks, unchecking "Check for publisher's certificate revocation option", got better performance during opening and queries on my Win7 x64.
Thanks a lot!
I'm developing on a machine that's extremely firewalled, where only the addresses strictly necessary for performing my job are accessible and, indeed, disabling the CRL check fixed it and sped up a bunch of other things, too.
Thanks man
unchecking "Check for publisher's certificate revocation option", got better performance during opening and queries on my machines.
Post a Comment