concurrent query question

[luffyxue55]

Hello, I have a little problem when I use tortoise-orm query.

I am using fastapi-0.112.0, tortoise-orm-0.21.5, and the database is sqlite3 or postgreSQL.

I have a query interface that uses prefetch_related to associate the query data in the main table and the child table that meet the query conditions.

Now the front end uses different query conditions to obtain the database data, and the front end requests are concurrent.

At this time, it takes much longer to obtain the query results through the following code than a single query:

templates = await (
        TemplateModel.filter(type__startswith=template_type, deleted=False).order_by('order_number').all()
        .prefetch_related('properties'))

Is there any way to optimize it?

[luffyxue55]

I found a way for postgreSQL to handle concurrent queries, but I couldn't find it for sqlite. Does sqlite not support it?

[henadzit]

@luffyxue55

At this time, it takes much longer to obtain the query results through the following code than a single query:

Could you please elaborate what you mean? Could you please provide both queries?

I found a way for postgreSQL to handle concurrent queries, but I couldn't find it for sqlite. Does sqlite not support it?

sqlite supports concurrent queries.

[luffyxue55]

Could you please elaborate what you mean?

The TemplateModel table has a subtable named Property. When querying TemplateModel, I want to query all its subtable data in a related manner. The query is initiated by the front end, and my query interface is called concurrently. My database connection is flexibly configured, it can be sqlite or postgresql.

Could you please provide both queries?

The query statements are as follows

templates = await (
        TemplateModel.filter(type__startswith=template_type, deleted=False).order_by('order_number').all()
        .prefetch_related('properties'))

sqlite supports concurrent queries.

What parameters are configured to achieve this?Just like the minSize and maxSize added when postgresql connects.

[luffyxue55]

Could you please provide both queries?

A single query refers to a non-concurrent call, and the actual query statements are the same

[henadzit]

@luffyxue55 sorry for the confusion, it doesn't seem like parallel queries with sqlite are supported by tortoise ORM.

SqliteClient.acquire_connection is called for every query and it returns a ConnectionWrapper https://github.com/tortoise/tortoise-orm/blob/0ddf8d327949be3ad812c41e4215c85b997214b9/tortoise/backends/sqlite/client.py#L148

And ConnectionWrapper acquires a shared lock from SqliteClient https://github.com/tortoise/tortoise-orm/blob/0ddf8d327949be3ad812c41e4215c85b997214b9/tortoise/backends/base/client.py#L235

[luffyxue55]

@henadzit Thanks for your answer. Is there any concurrent support plan for sqlite database?

[henadzit]

Is there any concurrent support plan for sqlite database?

I'm not a maintainer of tortoise-orm, I'm just researching the subject and trying to be helpful, so I cannot answer that. A few thoughts from me though:

• there is a difference between parallel and concurrent. At the moment querying with sqlite is concurrent but not parallel.
• tortoise-orm relies on aiosqlite and aiosqlite is "using a single, shared thread per connection. This thread executes all actions within a shared request queue to prevent overlapping actions.". So aiosqlite executes one query at a time per connection. Potentially a pool of connections can be introduced in tortoise but it feels weird to mix async and threads.
• It does not seem like it is popular to use async with sqlite. Async shines when networking is involved.

Could you please talk more about your problem? Are you running into an issue with the database not keeping up with the workload? In general sqlite should be quite fast because it's local.

[luffyxue55]

Is there any concurrent support plan for sqlite database?

I'm not a maintainer of tortoise-orm, I'm just researching the subject and trying to be helpful, so I cannot answer that. A few thoughts from me though:

• there is a difference between parallel and concurrent. At the moment querying with sqlite is concurrent but not parallel.
• tortoise-orm relies on aiosqlite and aiosqlite is "using a single, shared thread per connection. This thread executes all actions within a shared request queue to prevent overlapping actions.". So aiosqlite executes one query at a time per connection. Potentially a pool of connections can be introduced in tortoise but it feels weird to mix async and threads.
• It does not seem like it is popular to use async with sqlite. Async shines when networking is involved.

Could you please talk more about your problem? Are you running into an issue with the database not keeping up with the workload? In general sqlite should be quite fast because it's local.

Thank you very much anyway. This is my first time to use Python language for development. Our development team chose fastAPI, sqlite3 and tortoise-orm in technology selection, so I don’t know if my use is correct. The front end calls the interface I provided, and then I use tortoise-orm to query the data in the sqlite database. This operation cannot be completed asynchronously. The front end needs to get the data set synchronously.