0

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.

Jihef
  • 3
  • 2

1 Answers1

1

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

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • Thanks for answering. I know your tools dealing with SQL Compact but hadn't your FAQ concerning this issue. Unfortunately I can't do any of that because the database is generated on desktop an a daily (maybe more often) basis and it has a LOT of indexed strings. I will consider using replication but only in download direction (which can be done by resetting the subscription) – Jihef Oct 13 '15 at 07:35
  • You can also use RDA (simpler than replication) – ErikEJ Oct 13 '15 at 11:59
  • RDA vs Replication : Do you have an idea of which is faster ? It will be used to download and convert and entire SQL Server DB to SQL Compact on a PDA. The local DB will be changed locally but each time a new pull or synchronize is initiated all local changes will be discarded (because modification of the data will be tracked by another channel). Also the SQL Server DB will be built by an ETL from SAP and act just as a buffer between the PDA application (which needs to retain the legacy of the schema) and central data. – Jihef Oct 13 '15 at 14:06
  • When using RDA you download entire tables, where as Merge repl can download just changes. But it depends on how you are moving data on the server – ErikEJ Oct 13 '15 at 18:51