3

Possible Duplicate:
SQL Server CE 4.0 performance comparison

I am evaluating whether to use SQL Server CE 4 or SQLite as local cache for a desktop app. I ran a simple test to see how they will fare under pressure, since speed is very important to the app. The test consisted of dumping random data into the table below and then querying it - loop 500 times. 

CREATE TABLE [Lines] (
    [KeyHash] NVARCHAR(4000) NOT NULL,
    [Method] NVARCHAR(100) NOT NULL,
    [Value] TEXT  NOT NULL,
    [AddedOn] TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,
    Primary Key ([KeyHash], [Method])
)

The code to insert data was fairly simple. 

using (var conn = GetConnection()) {
  using (var cmd = conn.CreateCommand()) {
    cmd.CommandText = "INSERT INTO Lines(KeyHash, Method, Value) VALUES (@KeyHash, @Method, @Value)";
    PopulateWithRandom(cmd);
    cmd.ExecuteNonQuery();
  }
}

The code to query data was fairly simple too. 

using (var conn = GetConnection()) {
  using (var cmd = conn.CreateCommand()) {
    cmd.CommandText = "SELECT Value FROM Lines WHERE KeyHash=@KeyHash AND Method=@Method";
    cmd.Parameters.AddWithValue("@KeyHash", key);
    cmd.Parameters.AddWithValue("@Method", method);
    return (string) cmd.ExecuteScalar();
  }
}

The results were kind of crazy. SQL Server CE finished in 18 seconds, while SQLite completed everything in 4.7 seconds.

Am I missing something simple or is SQLite just that much faster?

P.S. Full results:

SQL Server CE 4 
Total: 18032 milliseconds 
Insert: 10069 milliseconds
Query: 6940 milliseconds

SQLite 
Total: 4739 milliseconds 
Insert: 3248 milliseconds 
Query: 496 milliseconds

I should not that I open and close the connection (e.g. using the using construct) on every operation.

Community
  • 1
  • 1
AngryHacker
  • 59,598
  • 102
  • 325
  • 594
  • @Darin Dimitrov - I don't think the results in the linked question are legit - they state that SqlCe is almost 1000x faster (in some cases) than SQLite. That is not what I am seeing at all, in addition to making a dangerously little amount of sense. – AngryHacker Sep 01 '11 at 17:51
  • the code is not the same neither. They use transactions. You don't but you should. – Darin Dimitrov Sep 01 '11 at 17:53
  • 1
    I've seen 4-10x speed on SQLite compared to CE on large batch operations and single queries. SQLite is immensely fast. However, the main problem I've seen is that CE's startup time is immense compared to SQLite. Has that been considered. – Deleted Sep 01 '11 at 17:59
  • 1
    @Chris Smith - You might be on to something - the start-up time may be the culprit here, because I open/close connection on every operation. – AngryHacker Sep 01 '11 at 18:02
  • @Darin Dimitrov - Why should I use a transaction? It's a single insert and atomic in nature to begin with. Also, one of the comparisons in the linked article is without transactions. – AngryHacker Sep 01 '11 at 18:02
  • Keep the (or a) SQLCE connection open from app start to app end. – ErikEJ Sep 01 '11 at 19:16
  • @EricEJ That is not the scenario my app will use, thus I was targeting a specific type of benchmark. – AngryHacker Sep 01 '11 at 20:38
  • But it is opening the connection that takes the time, open another connection then, and keep it open until your app closes! – ErikEJ Sep 07 '11 at 11:21

0 Answers0