I finally decided to go ahead with FastSqlCe, an alternative data access layer for SQL CE 2.0. The first go at it was when I decided to compare table insertion performance between a prepared SQL statement and a raw table insert. Comparing a native C++ implementation with a managed one is just not fair, so I decided to write a very simple layer and P/Invoke it. The results were stunning: a raw table insert can be up to five times faster than a prepared SQL INSERT statement. Five times. Of course, the INSERT statement had parameters.
Then I tried filling in a DataTable. The early results were also stunning: managed code was faster. Can't be, can it? After revising and tweaking the code I noticed that the less you P/Invoke, the better performance you get. After a full morning of tweaks I got the better of it and now I can (grimly) smile after squeezing a 15% improvement. Hey, native is faster.
But then I noticed how some SQL CE features are missing altogether, like schema management. How do you rename a table? How do you rename a column? How do you check if a column is marked as IDENTITY? Well, there may be some room for another data access layer after all. Enter FastSqlCe. Enter? Hum. It's not ready yet, sorry. But you can take an early look at it here: FastSqlCe online documentation. Comments and suggestions are welcome. I will post an early sample as soon as it is ready.