SQLCE Connections: Keep them open or let them close?

Question

Keeping performance on a mobile device in mind, what do you think is the best approach with an SQLCE Connection. Keep it open for the duration of the application or let it close whenever a call to the database is needed.

Obviously this depends a little on the nature of your application however I'm curious to hear what the group has implemented and why.

Answer 1

For my device, I kept it open during the lifetime of the application. If the functionality of the application depends on the SQLCE connection throughout, this makes sense.

Answer 2

Get Late, Release Early. Open the Connection as late as possible in the operation, and close it as soon as is possible. Open SQL connections are not good.

Answer 3

You should definitely watch Steve Lasker's PDC08 session about SQL Server Compact Edition. There's a part about performance, best practices and his own benchmarks (~37:00).

In short, keep it open, try to reuse commands, and use SqlCeResultSet for bulk operations.