2

I've built an app that uses SQL Server CE 4.0 by default, but can also use a separate SQL Server (SQL Server Express).

I've deployed it to about 10 computers around the office and it works fine except on 2 of them: the VP and a QA guy, of course :-/

On these computers, the database accesses are taking around 30 seconds each! If I install SQL Server Express 2008 R2, and tell my app to use it instead, then it runs very fast.

  • Both problem machines are WinXP SP3, but most of the working PCs are XP also.
  • I'm using C#.NET 4.0 App uses private deployment of SQL Server CE via ClickOnce.
  • I'm using DbProviderFactory to allow access to both SQL Server Express or SQL Server CE with the same query text (or data adapters).
  • Most of the queries are simple. A few use DataAdapter
  • Almost all of the queries eventually return data, but I saw one in my log file that returned empty, without an error/exception, but should have had about 20-40 rows and about 80 columns.
  • For every query, I create, open and close the connection. I am unclear if this is required/recommended/not-recommended with SQL Server CE.

I can't understand why this app works on most PCs, but not on these few.

Any ideas are welcome.

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
XKCD137
  • 357
  • 4
  • 14
  • 1
    Can be lots of things: Amount of available memory, hard disk speed/fragmentation, other running programs, antivirus, etc. etc. – D Stanley Dec 03 '13 at 15:27
  • 1
    DO keep an unused connection open for the lifetime of your app with SQL Server Compact. – ErikEJ Dec 04 '13 at 11:56
  • 1
    @ErikEJ - Thank you for all your blogs and forum posts. I would never have gotten this far without you! Do you have any Best Practice advice for coding a multithreaded app that can be user-configured to access either SQLCE or SQLExpress? – XKCD137 Dec 04 '13 at 14:42
  • 2
    Yes, if using SqlCe, open an unused DbConnnection on the main thread, and only close it on app exit. Create new DbConnections per thread. This approach will also work well with SQL Server. – ErikEJ Dec 05 '13 at 07:53

2 Answers2

3

The intermediate cause and workaround for this problem is with User access to .NET cryptography files used by a password-protected database. The problem was solved by changing the permissions on the folder.

You can open command prompt with Run-As Admin and issue this command (Win XP)

 cacls.exe "C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys" /E /G Everyone:R

I also added this comment to my code near the SQLCE connection string code:

 /**************************************************************************************
 * To get rid of the slowdown on non-admin users you need to grant them read access to this folder:
 * C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys  (XP, 2003)
 * C:\Users\All Users\Microsoft\Crypto\RSA\MachineKeys  (Windows 7 or 2008)
 **************************************************************************************/

This issue is mentioned here: SqlCe opens slow under a limited user account in Windows XP

And here: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/eb6a01a7-0bfd-41e3-b4c8-34581c5ccaa3/sql-compact-4-locks-up-30seconds-when-accessing-encrypted-database-on-windows-xp-when-running-in?forum=sqlce

Note: on the problem machine, the ...\MachineKeys folder was visible in WinExplorer, but the files couldn't actually be read. "Everyone" had "special" permissions that didn't allow "Read". I think .NET may have been installed by Domain admin, not local admin.

This is only a workaround. I still don't understand why the folder had weird permissions on a few machines but not the rest. I am deploying with ClickOnce, so I can't change the folder permissions automatically.

I have only seen this problem with XP, and other posts imply that this is just an XP problem, so hopefully I won't have to worry about it for long...

Community
  • 1
  • 1
XKCD137
  • 357
  • 4
  • 14
  • I've also found that it works to remove or rename the MachineKeys folder, as long as this doesn't mess up any other programs (like Visual Studio). It seems that the app is looking for the file in the MachineKeys folder. It has directory-listing permissions, so it then tries to read the file. Reading the file fails because it doesn't have Read permission, so it retries for 30-ish seconds. If the file/folder is not even there, then it gives up immediately and the 30 second pause is eliminated. – XKCD137 Dec 06 '13 at 14:26
2

I had a similar problem with SQL Server CE. I found that installing the latest version from http://www.microsoft.com/en-us/download/details.aspx?id=17876 resolved the problem

I suspect that those machines already have an earlier version of SQL Server CE installed in the global assembly cache, and that the earlier version is being used instead of the locally deployed version.

Also, check the information from Lingzhi Sun in this post. Aparrently if the database is used on a different operating system from the one on which it was created, this can cause performance problems. This blog post may also be helpful.

Olly
  • 5,966
  • 31
  • 60
  • Good idea. I've followed ErikEJ's blog for explicitly specifying whether to use GAC or Privately Deployed SQLCE. http://erikej.blogspot.com/2011/06/sql-server-compact-private-deployment.html I also added some code to log info about the connection when the DB Provider is established. The log confirms that the correct version of SQLCE is being used, so this is not the cause of my problem. – XKCD137 Dec 04 '13 at 14:30