I have a large (40 Mo) SDF database.
Some queries are very long, but can become quick after some time.
I would like to try to tidy up my indexes but I only find information for SQL Server and not for SQL CE.
+1
A:
Not exactly sure what sort of "tidying" you think needs to be done - the index won't be holding unnecessary records or anything in it. If you simply want to recover potentially empty space in the database (indexes and tables), you can use the either the Compact or Shrink methods in the SqlCeEngine class.
ctacke
2010-04-27 15:52:40
Well, when my mobile application starts, some queries take up to 5 minutes, but after 3/4 queries on the same tables, they are one in a few seconds. So I though maybe at first queries, database is reindex or something like that. So I would like to find a proper way to start this "reindexing" process manually when the application synchronise instead of when I actually need the data.
Jalil
2010-04-28 14:23:51
A query that takes 5 minutes for a mobile application sounds more like a database design problem. Have you manually added indexes?
Jason
2010-05-05 22:34:20
Yes there are indexes. And after the first query of each type (30 secondes to a few minutes), all queries of the same type execute very quickly (10 seconds max).
Jalil
2010-05-08 11:41:38
Of course I have no problem at all when the database is lightly loaded. But when fully loaded : - 50 Mo - Some tables can be +100 000 recordsAnd some of my queries have JOINs.
Jalil
2010-05-08 11:53:45
Are you facing a index rebuild on the first opne of the database - do you create the database file on another platform?
ErikEJ
2010-05-10 15:35:56
+1
A:
On the first execution of a SqlCeCommand instance the SQL CE engine needs to generate a query plan for that query. The first execution will always be slower then subsequent executions. You can also re-use command instances and call Prepare on the SqlCeCommand to mark the command for query plan generation.
TGRA
2010-09-29 21:08:22