I'm building a .NET 4.5 (winforms) application that uses LocalDB to work with a local MDF file, using this connection string:
Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\DB\DatabaseFile.mdf;Integrated Security=True;MultipleActiveResultSets=True
When I run my application, the first SQL query takes some time to execute - nothing drastic, about 2 or 3 seconds. After that, all next SQL queries are executed instantly. I assume that the extra seconds during the first execution are needed to attach the MDF file to a local SQL Server service. Right?
I noticed, however, that if 10 minutes (or so) pass since an SQL query was last performed, the next SQL query will again take those 2-3 seconds more to execute. I assume that after some idle time, the MDF gets detached, and when the new SQL command is called, it re-attaches it once again.
I'm wondering, is there a way to override this behavior?
I know I could create a Timer, that performs a simple query every few minutes, but is there a better, cleaner solution?
I would more suspect that your data / index is no longer in memory.
You could run a very fast query like select 'a' to know if it was the connect time.
As for forcing your table / index to stay in memory.
I would so much advise you to let .NET and SQL do their own memory management.
2-3 seconds is not much.
Look at the query plan - maybe you can make it faster (even not in memory).