I ran into a strange behaviour with a Microsoft SQL Compact 3.5 database. In the application on which I am working I need to create a SQL compact database on a desktop PC and send it to several PDA (running WinCE 5).
The database is quite heavy (300 MiB).
The problem is that when I first open the database on the PDA it takes more than 10 MINUTES just to open it. After that no problems, to open, read, seek into the database. Just the first opening is very long. I tested opening it with my own soft and with the query analyser on the PDA with same result.
I also found that if I copy back the database to a desktop PC, opening it is just a bit longer (hardly noticeable). Then I copy the opened file back to the PDA and the first opening takes 10 more minutes.
I also tested with a shrunken database (using shrinking operation) and I gave up after 40 minutes of waiting.
Is it a known problem ? Am I doing something wrong ? And is there a way to prevent that from happening ?
Thanks.
Yes, this is a known issue, caused by OS differences
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)
Avoid indexes with string based keys (probably not possible in most cases)
Postpone index building until during or after deployment
http://erikej.blogspot.dk/2013/08/faq-why-is-opening-my-sql-server.html