Performance issue computing query params

I have a complex python-sql query with subqueries, joins, coalesce, case, round, groupby, etc. A lot of python-sql classes which uses params. All this become in ~250 query params.

When I try to evaluate query.params it takes ~9min long. Doing some prints in “params” method it does not freeze at one point but it is iterating all the time and I cannot find the problem. Probably the problem is the query complexity.

So I would like to know if is possible to avoid the use of params, for instance inserting these values on the query. Or use named parameters due to most of them are repeated and it will reduce the number of params.

What are the types of parameters?

No for security reason. The parameters must be escaped by the DB-API library.

This would mean that for each parameter we would need to search for identical parameter to see if it could be reuse. So it means adding a n log(n) operation.

To send to the server at the cost of more computation in python-sql.

The types are integers, strings, a couple of Nulls and dates.
I tested to replace dates with strings in format %Y%m%d with same result.

So what strategy do you propose?

For me your numbers are very strange. So we will need more information about the query.

here you can find the SQL and params

Could you precise if it is the creation of the SQL string and parameter that are slow or the execution on the database?

The execution of the SQL in database (p.e. pgadmin) takes 3s.
The code line print(query.params) takes 9min.

Could you provide some code in order to reproduce your issue?

I have extracted part of the python-sql code (it is implemented in several modules) with no Tryton dependency and uploaded here.
This example is not taking so much time (90s) as the complete query but it is representative.

It seems the problem is with join of subqueries (commenting them the time decreases).

1 Like

Here is Slow params generation with a lot of nested joins (#90) · Issues · Tryton / python-sql · GitLab

I’m doing some more tests but for now it works like a charm!

This topic was automatically closed 12 hours after the last reply. New replies are no longer allowed.