How can I quickly open my connection to SQL Server CE?

Question

I need help, I have a SQL Server CE database in my project, and I open the connection when loading the first form and keep it open, you know the usual one:

if (con.State == ConnectionState.Closed) { con.Open(); } 

It works great, but the first time it opens a connection, it lasts a long time.

My question is: there is no way to make this opening process much faster so that my users do not fall asleep waiting for it to open?

Thanks in advance...

Answer 1

This API can have a bunch of overloads in the open command, but what happens under it is almost the same. If you do not switch to a completely different API or another db, there is no way to "speed up the process."

The only thing you can do is mitigate the problem by moving the connection open to any other part of the application’s life cycle, when the user may not notice or delay the delay.

One trick that is widely used is to implement this small hand in order to complete your initialization while the user screen is displayed. The screensaver may have some fancy graphics and text to “entertain” the end user. You will even see that the pop-up screen is updated with what the application does at this particular time. Not very useful, but may be interesting enough for the user to distract from the delay.

I did this once when users complained about the initialization step for too long. After I posted the change, end users vowed that the application works much better. This is actually not the case. I only manipulated perception.

Answer 2

There are several reasons why opening an SQL Compact connection might be too slow:

1: the database was created on a different platform (for example, Windows 7 => Windows XP)

2: ACL (access control list) in the RSA folder is damaged

3: Incorrect Internet proxy configuration

I have much more details and suggestions for a workaround in my blog post: http://erikej.blogspot.dk/2013/08/faq-why-is-opening-my-sql-server.html