PDC 2008 - Embedding SQL Server Compact In Desktop And Device Applications

This week I presented a session on SQL Server Compact.  Video Recording of the session is here.  The session focused on 3 main topics:

1. Why SQL Server Compact "changes the game" for how you might think about leveraging a database in your app
2. Performance of SQL Server Compact
3. Interesting scenarios you can use a local database when it's as easy as Compact.

Powerpoint of the session 

For the "why", I mostly focused on the deployment aspects of SQL Server Compact, including the new 64bit deployment scenarios.

For Performance, I took the previous per demo I've been showing and adding some more tests, including a comparison to SQL Server Express and inserts using the SqlCeResultSet.  In the performance section I focused mostly on the architecture differences between SQL Server and Compact.  SQL Server has a long running service and connection pooling.  Compact doesn't.  SQL Server caches query plans in the service, compact caches them in the SqlCeCommand object.  By understanding the architectural differences, you can get a dramatic performance benefit by just doing a few simple steps. 

A few performance comparisons

Connection Pooling

Test 1:
Traditional inserts, following the standard model of open/close connections as fast as possible.  For this scenario, the developer assumes a connection pool, which is provided by the SqlClient (MDAC/SNAC) libraries installed on each users computer.  The problem is Compact doesn't have built in connection pooling, so for each operation, Compact loads and unloads the engine.  It's somewhat equivalent to attaching and detaching a SQL Server database (MDF), or starting/stopping the service.

public

  voidInsertCustomer(intcustomerId, string customerName) {
  using (SqlCeConnection conn = new SqlCeConnection(_connectionString)) {
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = Queries.Sprocs.CustomerInsert;
cmd.Parameters.AddWithValue("Id", customerId);
cmd.Parameters.AddWithValue("Name", customerName);
conn.Open();
cmd.ExecuteNonQuery();
}
}

Test 2:
In Test 2, I simply modified the connection string for SQL Server to disable Connection Pooling (pooling=false) Figured it wasn't fair to stop and start the SQL Service or attach/detach.  In reality, what we're really comparing is the cost of creating connections.  For SQL Server, we're simply establishing the networking stack, which is a bit costly.  For Compact, we're actually loading the engine.

Test 3:
Here I'm simply keeping the SqlCeConnection open.  Since Compact doesn't have connection pooling, it means I do need to keep the connection open alive and open.

Table showing different tests, and the number of inserts per second.  
Note: This isn't meant to be a defacto performance benchmark.  Simply an example of how the archtiectural differences are targeted to different scenarios.  Your test will vary based on CPU power, RAM, types of data, speed of hard drive, humidity in the air, oxidation on your fingernails and pins within the mother board, ...

In the above table, we can already start to see where Compact can jump ahead, quite significantly.  When disabling Connection Pooling, SQL Server drops from 810 inserts/second to 122.  While Compact jumps from 6 inserts/second to 2,839.

Query Plan Caching

The next area is comparing Command Caching between Compact and SQL Server.  SQL Server caches query plans within the engine.  This means that different threads, processes or completely different clients can benefit from each other.  It also means the server is holding onto more resources, just in case someone might want them.  This is where we really start to see the differences between Compact and Express.  Compact, being an embedded database, optimizes for resource usage.  It's not that you can't cache query plans, it's just that you, as the developer need to do this as it's not "by default". 

Test 1:
Query Plan Caching w/Parameterized queries.  I didn't take the time to invalidate the cache for SQL Server each time, so I'll just simply show that Inserts/Second take advantage of Command Caching as well

Source code for the Performance Test Harness

Trace Listener

To monitor what's happening in the field, I demonstrated how you can use a SqlCeTraceListener with sync to capture errors, KPIs or other interesting data in the field, and bring it "home"

Source code for the SqlCeTraceListener

Using SQL Server Compact as a Doc format

Source code

As always, PDC was a blast talking with customers.  Lots of great sessions, and too many to list.  Luckily, you can see them all at https://www.microsoftpdc.com/ Gotta run to a Halloween party, but wanted to get some info up quickly.

Steve