1

I'm developing an ASP.NET Core web app (Blazor Server-side to be exact) and I was checking a .NET based embedded database called LiteDB and I noticed that it still lacks async calls while most SQLite wrappers have them (for example Dapper ORM for SQLite exposes many async methods)

If I'm not mistaken (please correct me if I'm wrong), the whole point of using asynchronous calls (let's say async/await in C#) is to free up the threads from waiting for the completion of IO operations (let's say querying a database).

The above scenario makes sense when in case of the said example, the database is in another machine or at least another process of the same machine because we are effectively relegating the job to something else and execution thread can do other jobs and come back to the result when it's ready.

But what about embedded databases such as SQLite (or the one mentioned above: LiteDB)? These databases run in the same process as the main application so any database processing (let's say querying) is still done by the threads of the application itself.

If the application is a classic GUI based app (let's say WinForm), using asynchronous calls would free up the main thread from being blocked and app becomes non-resposive and still understandable but what about the context of ASP.NET Core app in which every request is processed in a separate thread*?

*My question is that why use asynchronous calling when the app itself has to do the database processings too and therefore a thread has to be kept busy anyway;

wiki
  • 1,877
  • 2
  • 31
  • 47
  • 2
    Do you even have the option to talk asynchronously with an SQLite database? Does the driver have asynchronous methods to use? – Theodor Zoulias Jul 17 '22 at 10:18
  • I didn't talk about the SQLite driver. I said that most SQLite wrappers expose async functions (for example Dapper ORM) – wiki Jul 17 '22 at 11:54
  • Could you confirm that the asynchronous methods of the Dapper ORM that wraps the SQLite, are actually implemented asynchronously? They might be asynchronous facades over synchronous methods: `return Task.FromResult(data)`. You can confirm it by storing the `Task` in a variable, and checking its `IsCompleted` property. If the property is always `true`, you are dealing with a facade. – Theodor Zoulias Jul 17 '22 at 12:05
  • That's the whole point of my question; Are those async methods just facades? – wiki Jul 17 '22 at 12:07
  • If you are asking whether every existing embedded database has an async-enabled driver, so that every existing ORM that has async API can wrap it truly asynchronously, then your question is too broad IMHO. A complete answer might need to be several pages long. Could you narrow it a bit? – Theodor Zoulias Jul 17 '22 at 12:16
  • Let's just narrow it to the most famous one: SQLite. – wiki Jul 17 '22 at 12:19
  • 1
    Seems related to my question: https://stackoverflow.com/questions/68742424/how-to-deal-with-sqlite-in-an-asynchronous-project – wiki Jul 17 '22 at 12:21
  • 1
    Yea, if you narrow it to SQLite (by editing the question), then the [linked question](https://stackoverflow.com/questions/68742424/how-to-deal-with-sqlite-in-an-asynchronous-project) might be a duplicate. – Theodor Zoulias Jul 17 '22 at 12:41
  • 1
    Putting it another way: yes it would be better to have real async functions, but no SQLite at the moment does not offer it. – Charlieface Jul 17 '22 at 15:19

2 Answers2

2

Context

Microsoft's Async limitations (from 09/15/2021) states:

SQLite doesn't support asynchronous I/O. Async ADO.NET methods will execute synchronously in Microsoft.Data.Sqlite. Avoid calling them.

Instead, use a shared cache and write-ahead logging to improve performance and concurrency.

More

what about the context of ASP.NET Core app in which every request is processed in a separate thread*?

*My question is that why use asynchronous calling when the app itself has to do the database processing too and therefore a thread has to be kept busy anyway;

The first point is that it's not true that every request is processed in a separate thread. Using real async/await allows serving more requests than the number available treads.

Please remember that async/await does not equal multi-threading, they are separate and different; with overlaps.

It's not just the overall volume work that decides if using multiple threads is worth it or not. Who is doing what is very important. Even when all the cooking and serving is happening in the same restaurant you wouldn't want to dine in a busy restaurant where waiters do all the cooking.

You're right to think that the async/await is not beneficial with SQLite because under the hood it's synchronous but the point is that the original executing thread is never freed to do other work; the point is not that the work has to be done by the application itself (but could be done by new/dedicated thread).

tymtam
  • 31,798
  • 8
  • 86
  • 126
-2

Async(Await) are not only about free up threads if you have to do smth on another machine. Like example, when you want write text in file, you can do it async.

        var text = "mytext";
        File.WriteAllTextAsync(@"C:\Temp\csc.txt", text);

It free up your thread and will done by thread from thread pool

Same logic in SQLlite. You can do smth by another thread. So you can use it to improve perfomance and etc

You can check source code SQLLite and check how it works https://github.com/praeclarum/sqlite-net/blob/master/src/SQLiteAsync.cs

Dmitry Machikhelyan
  • 15
  • 1