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 the 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?
A:
Make sure to keep a connection to the database open for the duration of your application. Opening a SqlCeConnection is an expensive operation.
ErikEJ
2010-05-30 15:21:23
It is not ten seconds expensive.
AMissico
2010-06-05 10:29:03
Well, I encountered performance problem using SqlServerCE as cache database in windows service application. I used profiler to determine the cause of performance penalty. And I found that SqlServerCe connection closing was the cause. Opening connection is time consuming, but closing connection takes about 8 times longer!Here's about performance: http://stackoverflow.com/questions/386223/sqlce-connections-keep-them-open-or-let-them-close.
Dmitry Lobanov
2010-09-01 11:27:34
A:
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.
AMissico
2010-06-05 10:43:35
I posted this question too quickly before actually doing more research (shame on me). You are correct. I am loading hundreds of records for a few different views and it does seem to be windows paging that is causing the performance problem.
Wili
2010-06-08 13:01:22