Is it really beneficial to use asynchronous calls when using embedded databases such as SQLite in th-CodePudding

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;

CodePudding user response：

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.

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).

CodePudding user response：

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

Context

More