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");
}
Pure speculation here, but a few things come to mind:
_context.Database.OpenConnection()
before the query)_context.Database.ExecuteSqlCommand("PRAGMA journal_mode=WAL;")