Hello,
Our team is currently working on a WPF Desktop application with ActiveRecord and SQLCE 3.5 for backend. We've been experiencing some strange performance issues when executing queries or creating/updating records. In some cases opening a form which has a few grids and data elements that need to be populated, can take around 2 seconds!! This is not very ideal on a desktop application. We did some testing both with Active Record and Fluent NHibernate and found that given an extremely simple entity (2 string fields, no associations), a call to Create() will take something like 300ms the first time, and around 100ms the subsequent times. For a FindAll (given there are only 5-6 rows in the table), the first call takes around 200ms, and subsequent calls take around 80ms. SQLite was somewhat faster (in some situations around half the time), but SQL Server was considerably faster; around 5-6 ms for queries and inserts after the first call!
We dug a little further, the problem (amongst other things perhaps) seems to be related to session creation and releases under the hood. They seem to be quite slow for SQLCE and SQLite. For large sets of data, performance seems to be similar to SQL Server which makes me think the problem is not the execution of the query itself. But given all sorts of caching and session management that happens between ActiveRecord, NHibernate, and the database, I'm not quite sure where the problem actually lies.
Is this sort of performance normal with SQLCE (and SQLite)? Are there any tweaks that can be done to improve this? I would've expected these to be faster than SQL Server, and certainly not 100+ ms to perform a simple operation. Any hints or suggestions would be great!