SQL Compact performance on device

Question

My SQL Compact database is very simple, with just three tables and a single index on one of the tables (the table with 200k rows; the other two have less than a hundred each).

The first time the .sdf file is used by my Compact Framework application on the target Windows Mobile device, the system hangs for well over a minute while "something" is done to the database: when deployed, the DB is 17 megabytes, and after this first usage, it balloons to 24 megs.

All subsequent usage is pretty fast, so I'm assuming there's some sort of initialization / index building going on during this first usage. I'd rather not subject the user to this delay, so I'm wondering what this initialization process is and whether it can be performed before deployment.

For now, I've copied the "initialized" database back to my desktop for use in the setup project, but I'd really like to have a better answer / solution. I've tried "full compact / repair" in the VS Database Properties dialog, but this made no difference. Any ideas?

For the record, I should add that the database is only read from by the device application -- no modifications are made by that code.

Answer 1

Since the db is read only, and if the "initialized" db no longer inflates, I would go with simply putting it into the setup. Just confirming that your approach makes sense.

Answer 2

Yes, it recreates your indexes because the database was created or opened on a desktop computer. Copy your indexed database from the device and into your setup.

more info here: http://blogs.msdn.com/sqlservercompact/archive/2009/04/01/after-moving-the-database-from-one-platform-to-other-the-first-sqlceconnection-open-takes-more-time.aspx