Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

concurrent query question #1738

Open
luffyxue55 opened this issue Oct 14, 2024 · 8 comments
Open

concurrent query question #1738

luffyxue55 opened this issue Oct 14, 2024 · 8 comments

Comments

@luffyxue55
Copy link

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
Copy link
Author

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

@henadzit
Copy link
Contributor

@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
Copy link
Author

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
Copy link
Author

Could you please provide both queries?

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

@henadzit
Copy link
Contributor

henadzit commented Oct 17, 2024

@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

async with self.acquire_connection() as connection:

And ConnectionWrapper acquires a shared lock from SqliteClient

await self.lock.acquire()

@luffyxue55
Copy link
Author

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

@henadzit
Copy link
Contributor

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
Copy link
Author

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants