SQL Server Embedded Edition vs SQLite

Question

Can anybody shed some light on how these SQL products compare? No need for essays, only highlights such as "X supports this, while Y doesn't".

• How is efficiency affected as the database grows larger
  • Inserts
  • Updates
  • Selects
• What kind of transactions are supported
• What support is there for multiprocessing
  • Access from concurrent processes
  • Access from concurrent threads
• Data access is in-process or via server process
• Key feature of either product that is worth mentioning

Also, any (positive or negative) experiences from people who have used both products (don't want opinions - just observations).

The reason I'm asking is that it's hard to penetrate the marketing fluff surrounding Microsoft's embedded SQL product.

Thank you.

Answer 1

Based on your question "What support is there for multiprocessing":

• SQL Server CE and SQLLite are single-user DBs that work in your app and deployed as DLLs
• SQL Server (Express and above) (and MySQL, Oracle, Postgres et) are server engines where you submit a request

Either you have proper multiprocessing or you don't: not much grey area in between. The rest of your questions don't matter because of this.

An as example, SQLLite locks tables locking so you don't have much concurrency even if it nominally supports multiple processes

Edit, about SQL Server CE from SO

• "SQL Server CE in a multi user scenario - is this really, really stupid?"
• "What is the number of maximum concurrent connections for Sql CE 3.5?"

It looks like SQL Server Express is recommended for multi process access