Sunday, April 09, 2006

SqlCeConnection Keep Connection Open?

Consensus seems to point to keeping one instance of SqlCeConnection open for the duration of executation for performance reasons.
  1. SqlCeConnection guidelines (MSDN Forums)
  2. Performance with SqlCeConnection (Usenet)
  3. Data Architecture & Pooling (Usenet) <- this is for SqlServerCe 2003
Marcus Perry in his MSDN blog notes:
Creating and destroying SQL Mobile database connections is an expensive task and so the SqlCeConnection is designed to be a long lived, shared instance across the lifetime of the application. For a complex app, ideally the SqlCeConnection instance would be placed in a singleton wrapper class that manages access to the database.

Though this information comes from a Microsoft employee, it still seems contentious. Dave Hayden, a MS MVP, said in this MSDN forum post:
For 99% of all applications, the best practice is to open and dispose of database connections right when you need them and not to leave them open for the duration of the application. Open the database connection as late as possible and close/dispose of it as soon as possible.

Nicholas Paldino (a .NET/C# MVP) seems to agree with Dave:

You shouldn't keep a connection open and lying around. The SqlConnection class uses a connection pool in the background, and keeping your connection open is actually going to degrade your performance. You will want to create a new connection, open it, use it, and then close it for every operation that you do (or group of operations). You shouldn't be holding the connection open across operations. You will also notice MUCH better performance when you do this. The same thing holds with the command object. Don't use the same one. Use new ones. If you want to use a command object over and over again, you can, but use it for the same parameterized command. Don't keep changing the command string.

Both Dave and Nicholas are basing their advice on SQL Server's behavior (not SQL Server Mobile). SQL Server supports connection pooling, SQL Server Mobile does not. Again, from MS Employee Marcus Perry (link),
There is no connection pool used for local database access for SQL Mobile.

If only one SqlCeConnection is used, note that it is not thread safe. Thus, a multithread application must provide mechanisms to safely access the SQL database from multiple threads. There seem to be two approaches: one is to make the singleton wrapper class (mentioned above) thread safe and the other is to give each thread access to its own SqlCeConnection object. See these MSDN forum posts (here and here). Microsoft has yet to endorse a specific approach as a "best practice" (a discussion about this can be found here).

1 comment:

Tommy said...

Not sure if it's changed since the old version but in VS 2008 I created a small test app to time how long it takes to open and close connections.

The first time I open a connection it takes about 3 seconds and everytime after that it's almost instant.