SQL Server Compact Edition 3.5 performance

I am using SQL Server CE 3.5 SP1 in one of my client applications. When a user loads the program and starts using it, performance is fine. If the user lets the program sit idle for a while, it takes a considerable amount of time (10 or more seconds) for the program to respond. Every time the user asks for a new screen, a call is made to the SQL CE database to get the data for that screen. It seems like t开发者_如何转开发he hard drive may be going to sleep and then when the database is accessed, the hard drive has to wake back up. Is it possible to load the entire database into memory and work from that? Are there any other suggestions on how to increase performance?

I doubt very much the problem is SqlCE. It is a very fast database. In process. Moreover, I have loaded hundreds of thousands of records and I get the same performance if I was using SQL Express.

Can you load the entire database? Sure, that is what ADO.NET is for. Don't do it.

I suspect you have other issues. For example, are you pre-processing the data before loading the form, such as setting data-set relations, added expression columns to data-tables, and so on. It is also possible that the user's computer does not have enough memory and what you are experiencing is Windows page faulting. What you think is SqlCe accessing the databasse, is probably Windows swapping your application back into memory after being written to the paging file.

Make sure to keep a connection to the database open for the duration of your application. Opening a SqlCeConnection is an expensive operation.