SQL CE 4 very poor performance on XP SP3

Question

I've a SQL Compact 4.0 database with Entity Framework 4.3 and Encryption enabled with Platform Default mode and this is its connection string:

    Data Source={Database File};Password={MyPassword}; Encryption Mode=Platform Default;Persist Security Info=False;Locale Identifier=1065

When I execute a simple query like authenticating a user by their user name and password, under windows 7 or 8 it works perfectly with a reasonable performance. But if I run it in a Windows XP Service Pack 3 environment it executes the query incredibly slow. Takes about 1 or sometimes 2 minutes to authenticate the user.

XP Computer configuration is: Intel Pentium Celeron 2.8 MHz and 512MB RAM.
Windows 7 configuration is the same except that it has 1GB of RAM.

At the end I want to have a small database like the SQL CE with a good encryption support that works with EF 4.3. If any other database engines with this features exists and can solve this poor performance, it could solve my problem.

Thanks.

Answer 1

It could be 2 issues: Initial re-indexing if you are moving the database from a Windows 7 platform to Windows XP, or it could be broken permissions on the RSA folder, see this thread: http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/1b21bb10-1fa3-4954-87d2-f3fa61e8256a

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 2

Thanks ErikEJ,

I'd already read that post and checked them before I posted the question.

1. The user account that runs the application is in the Administrators group and also I've checked the RSA folder. It has "read" permission.

2. The re-indexing should be a one time thing (if I'm not wrong) so the next time it should be OK, but in my case it isn't. I can access the database and read from it (very slow).

Answer 3

Maybe it is a locale issue then, can you test with another (sample) database file, like Chinook for example (non Farsi)

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 4

Well, what if the Chinook works and I find out the problem is a locale issue? I have to store Farsi data.

Answer 5

Is it possible that the length of the password makes the operations slow?

Answer 6

Why don't you test without password protection

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 7

Thanks Erik for you support.

I've tested every combination of the connection string options such as encrypted, not encrypted, with locale and without it. It seems that there is nothing I can do to improve the performance simply by changing its options.

I've to add that I reviewed the process and noticed that the process will perform a heavy task (updating or inserting about 50 to 80 records). 

Here is a psudo code of it:

var tasks = context.Tasks.ToList(); // It has about 80 records

foreach(var task in [AnotherInMemorySource])
{
    if( tasks.Any(t => t.Id == task.Id) )
        context.Tasks.ApplyCurrentValue(task);
    else
       context.Tasks.AddObject(task);
}

Now is there any way to improve the performance of this task?

Thanks.

Answer 8

Check that any required indexes are there, and consider using TableDirect for the updates, perhaps. But updating 80 records should never be a long process.

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 9

I've ran a simple simulation application to see what is really happening 

this is what I have done:

1. Created a simple database containing a single table called Users which has some properties. The most important one is UserName which is indexed and is unique.

2. Created a Entity Framework 4.3.1 model 

2. Copied that database (which has 5 users) to two computers 

3. Ran SqlEngine.Shrink, Repair and Compact methods.

4. Tried to get the user with a specific user name with the following code: 

var username = "testuser1"
context.Users.SingleOrDefault(u => u.UserName.ToLower() == username);

5. Ran the code above 3 times (to conclude that index recreation isn't the problem)

Results:

Took about 00:00:00.2618813 on a computer with a Intel Celeron 2.4 and 256MB of RAM

Took about 00:00:13.3514114 on a computer with a Intel Celeron 2.8 and 512MB of RAM

Both computers have a clean Windows XP SP3 and .NET Framework 4.0 Client Profile with nothing else installed.

I have no explanation what is it happening here. Any suggestion will be appreciated.

Answer 10

I would use process Explorer to see what is blocking - antivirus ?? Also, what is the connection string used?

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 11

No antivirus, no other application. In both computers only windows and .net are installed. 

Data Source={Database File};Password={MyPassword}; Encryption Mode=Platform Default;Persist Security Info=False;Locale Identifier=1065

Answer 12

And same result without encryption? time to look at process Explorer...

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 13

I haven't check without encryption. Because in both systems the encryption was enabled therefor I think we could assume it isn't the problem. Am I right?

Also I have to add that the inserting the data isn't the problem. It has a little performance lost in the computer that has slow read but it can be ignored.

What should I looking for in the process explorer? I've not used it before.

Answer 14

use Process Monitor to log your process, and look for pauses with access denied or fil not found messages, http://support.citrix.com/article/CTX131825 (it is process monitor, not Explorer, my mistake)

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 15

Thanks Erik it was really useful. I finally find out what was the problem. For future reference, the problem was because of insufficient permission on machine key even though the user I was testing was an administrator.