title: SQlite terribly slow vs postgres with ASP.NET Core
body: <p>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. </p>
  
  <p>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.</p>
  
  <p>So I tried migrating to postgres and (shockingly), it can do the same amount of work in around 40-200 ms for each query.</p>
  
  <p>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?</p>
  
  <p><strong>EDIT:</strong> I've already read <a href="https://github.com/aspnet/EntityFrameworkCore/issues/5466" rel="nofollow noreferrer">this github issue</a>, 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:</p>
  
  <pre class="lang-cs prettyprint-override"><code>public async Task&lt;IActionResult&gt; mark([FromRoute] int id)
  {
      Foo foo = await _context.Foo.Where(m =&gt; m.id == id)
                                  .Include(m =&gt; m.RelatedTable)
                                  .SingleOrDefaultAsync();
  
      if (foo == null) return BadRequest();
      if (foo.relatedtable.Count &gt; 0) return BadRequest("No related records");
  
      foo.bar = 1;
  
      _context.Update(foo);
  
      await _context.SaveChangesAsync();
  
      return Content("success");
  }
  </code></pre>