SQlite terribly slow vs postgres with ASP.NET Core

asp.net asp.net-core-2.0 entity-framework-core performance sqlite

Question

In my ASP.NET Core 2.0 RESTful API which uses entity framework core, I have a function to which repeated calls are made, with several at once. The function itself is a simple update, where it flips the value of a record from 0 to 1 and back again and saves changes.

During course of normal operation, the page may make about 10 queries at once. If I use SQlite, for some reason, all queries return at the same time about 1000ms (!) after the calls.

So I tried migrating to postgres and (shockingly), it can do the same amount of work in around 40-200 ms for each query.

AFAIK, SQlite is supposed to be faster, since the data store is really small anyway (~100kb). So why is there such a large discrepancy here?

EDIT: I've already read this github issue, which says that all requests will be handled serially, so I/O blocking could be the playing factor, but I really can't see SQlite taking so much time for such a simple operation. The code I'm using:

public async Task<IActionResult> mark([FromRoute] int id)
{
    Foo foo = await _context.Foo.Where(m => m.id == id)
                                .Include(m => m.RelatedTable)
                                .SingleOrDefaultAsync();

    if (foo == null) return BadRequest();
    if (foo.relatedtable.Count > 0) return BadRequest("No related records");

    foo.bar = 1;

    _context.Update(foo);

    await _context.SaveChangesAsync();

    return Content("success");
}
1
0
2/7/2018 5:15:37 AM

Popular Answer

Pure speculation here, but a few things come to mind:

  1. SQLite doesn't support Async, so those calls are just adding extra overhead.
  2. Npgsql caches the compiled/prepared SQL statements whereas Microsoft.Data.Sqlite has to recompile the SQL every time (issue #5459 would help here)
  3. You might get better throughput by keeping the connection open between the query and update (call _context.Database.OpenConnection() before the query)
  4. Turn on write-ahead logging (WAL) using _context.Database.ExecuteSqlCommand("PRAGMA journal_mode=WAL;")
1
2/7/2018 5:59:43 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow