3 ms·

Interop overhead makes SQLite in .NET a lot slower than an embedded database, in my experience - though this may be a problem with the stock SQLite wrappers and

Interop overhead makes SQLite in .NET a lot slower than an embedded database, in my experience - though this may be a problem with the stock SQLite wrappers and not an unfixable problem.

miffy900 4y ago
In my experience the overhead is only noticeable if you use Entity Framework Core to interface with Sqlite. The native Sqlite wrapper libraries such as Microsoft's Microsoft.Data.Sqlite have faster performance than EF, but because you're forced to pass around SQL strings, most .NET dev's don't prefer to use them.
- eyegor 4y ago
  I would suggest using system.data.sqlite instead, it's the official version from sqlite and is actively maintained. Additionally, it's self contained whereas the Microsoft version requires separate dlls. Personally I've used dapper as a wrapper over sqlite and had decent performance but you do have to be aware of transactions and increase the cache size (and maybe set wal mode). There's also things like sqlite-net which looks promising but I haven't tried it so I can't comment on perf. Perf with litedb is pretty awful in my experience if you're using it as a schemaful db. But it's probably the wrong tool for the job if you're comparing to sqlite. I've yet to come across any embedded db that can do schema + document with decent performance.
  - halfbrite 4y ago
    Microsoft.Data.Sqlite makes it easier to use encrypted databases compared to System.Data.Sqlite out of the box. I am yet to find a modern up-to-date tutorial and implementation of System.Data.Sqlite that offers encrypted database support without making my brain melt.
- zmj 4y ago
  A few years ago, I needed faster SQLite interop than Microsoft.Data.Sqlite, and ended up writing my own: https://github.com/zmj/sqlite-fast https://github.com/zmj/sqlite-fast The ergonomics could be better, but this enabled query execution to avoid heap allocations and reflection.
billpg 4y ago
I wonder if anyone's working on compiling SQLite's C code into C# so it can run without interop.

Interop overhead makes SQLite in .NET a lot slower than an embedded database, in my experience - though this may be a problem with the stock SQLite wrappers and not an unfixable problem.

miffy900 4y ago

In my experience the overhead is only noticeable if you use Entity Framework Core to interface with Sqlite. The native Sqlite wrapper libraries such as Microsoft's Microsoft.Data.Sqlite have faster performance than EF, but because you're forced to pass around SQL strings, most .NET dev's don't prefer to use them.

eyegor 4y ago
I would suggest using system.data.sqlite instead, it's the official version from sqlite and is actively maintained. Additionally, it's self contained whereas the Microsoft version requires separate dlls. Personally I've used dapper as a wrapper over sqlite and had decent performance but you do have to be aware of transactions and increase the cache size (and maybe set wal mode). There's also things like sqlite-net which looks promising but I haven't tried it so I can't comment on perf. Perf with litedb is pretty awful in my experience if you're using it as a schemaful db. But it's probably the wrong tool for the job if you're comparing to sqlite. I've yet to come across any embedded db that can do schema + document with decent performance.
- halfbrite 4y ago
  Microsoft.Data.Sqlite makes it easier to use encrypted databases compared to System.Data.Sqlite out of the box. I am yet to find a modern up-to-date tutorial and implementation of System.Data.Sqlite that offers encrypted database support without making my brain melt.
zmj 4y ago
A few years ago, I needed faster SQLite interop than Microsoft.Data.Sqlite, and ended up writing my own: https://github.com/zmj/sqlite-fast https://github.com/zmj/sqlite-fast The ergonomics could be better, but this enabled query execution to avoid heap allocations and reflection.

billpg 4y ago

I wonder if anyone's working on compiling SQLite's C code into C# so it can run without interop.