I have something a little bit different happening.
I have a .net framework application that runs 3 queries against a SQL CE 3.5 database (it runs more but the majority of processing is done by 3 queries) and 3 indexes for the queries.
The performance is ok nothing special, but as soon as I open a connection to the database via query analyzer 3.5 on the device and leave it open while the application is running the performance becomes outstanding, almost instantaneous. As soon as I close the query analyzer connection to the database the performance goes back to normal, I can open the connection from query analyzer while my app is running and notice improved performance straight away.
Can anyone explain why I am getting a performance boost when query analyzer is open? As I would love to have that sort of performance all the time not only when query analyzer is open.
Couple of notes:
- I am not running the queries in query analyzer I am running them from the application, all I am doing is opening query analyzer and opening a connection to the .sdf
- The connection string in my app specifies the max buffer size to around 8900 (I'm not sure why I inherited the app)
- Each query is within a using statement for the SqlCeCommand, therefore the command is created just before the query is run then disposed of straight after