Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

Is it really beneficial to use asynchronous calls when using embedded databases such as SQLite in the context of an ASP.NET app? [duplicate]

Ask Question

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;

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, 2022 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, 2022 at 11:54 Yea, if you narrow it to SQLite (by editing the question), then the linked question might be a duplicate. Theodor Zoulias Jul 17, 2022 at 12:41 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, 2022 at 15:19

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

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

"It free up your thread and will done by thread from thread pool" -- There is no thread! – Theodor Zoulias Jul 17, 2022 at 11:14 @DmitryMachikhelyan your example isn't related to my question. File.WriteAllTextAsync is done by the underlying system (not the app); – wiki Jul 17, 2022 at 12:12