Chris at opennetcf.org has published a great article about SQL CE data access performance and how to improve the usage of lookup tables with data caching. He also concludes (in contrast to "common belief") that opening and closing connections does not have any impact on performance, using his tested data access method (a DataReader with a standard "SELECT" statement).
2 comments:
Erik,
You should check back on that article by Chris. There is a comment that sheds a lot of light on the whole "connection" open/closed performance issue. I've pasted the comment below.
Thanks,
==============================
Hi Chris,
For the connection performance, I'm going to guess you have a different connection already open. The way SQLce works is the first connection loads the database into shared memory. Think of it as starting the SQL Server Service for our big brother SKUs. Subsequent connections simply access the already loaded database. Sort of like using a connection from the connection pool. (but, no SQLce doesn’t have connection pooling). Since we have the best practice guidance for opening/closing a connection for our server skus, you can follow this practice with SQLce. However, you’ll want to open at least a phantom connection to keep the engine loaded. You don't ever have to use this connection object, just think of it as the "SvcHost"
This best practice for sharing a global connection was the best practice back in the SQLce 1.0 & 2.0 days when we only supported a single connection. With SQLce 3.0 (formerly SQL Server Mobile 3.0) we added multi-connection support, locking etc., so it is possible to read conflicting “best practices” if the version isn’t attributed to when the best practice was a recommendation.
This also accounts for the differences you and Chris may be seeing.
I’ll also add that sharing a SqlCeCommand object can help or hurt you. In SQLce 3.x, we cache the queryplan associated with the command. So, while you may want to re-use objects to help .NET CF manage garbage collection, constantly changing the CommandText of an existing SqlCeCommand can hurt you if you’re going to re-execute the same command multiple times as we'll toss the cached plan.
If you’re going to do something like the following, your best to open a single connection, and create two different commands. Ohhh, and yes, it’s best to use the parameters object
For each blahh in some result
Insert into Orders
Insert into OrderDetails
Next
Hope that helps take some mystery out of some of our performance thingys
Thanks for the clarification, "Joker". I have posted a comment to Chris' article, that demonstrates how to make SQL Compact perform quickly (and avoid caching issues). It also demonstrates how to get the TOP 1 records in SQL Compact 3.1
Post a Comment