Very slow SQL query

Support Developer
1

Hi,
I need to execute SQL query like this:

    main.join(add1, 'LEFT', condition=main.add1_id == add1.id
        ).join(add2, 'LEFT', condition=main.add2_id == add2.id
        ...
        ).join(par1, 'LEFT', condition=(main.year == par1.year)
            & (main.month == par1.month)
        ).join(par2, 'LEFT', condition=(main.year == par2.year)
            & (main.month == par2.month)
        ...
        ).select(...)

There are about 10 add-tables and 5 par-tables in the query. Its execution time is 8 seconds.
I just printed out this query and executed it in phpPgAdmin. Its execution time is 0.4 seconds.
Why does this query take 20 times longer to complete in Tryton-environment?

2

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

3

I excluded par-tables from the query and left only add-tables. Execution time is 0.6 seconds.
I excluded add-tables from the query and left only par-tables. Execution time is 0.2 seconds.
What can I do to speed up my full query? The full query contains 160 params.

4

Did you try the solution provided by @ced in the previous message?

5

Do you mean this?

I didn’t understand how to catch the exception.

6

You should apply the patch of the Merge Request (you can get it by clicking on the “Code” button in the top right of the screen and selecting “patch”) to your python-sql module, or wait for a new release and update using your preferred method.

7

OK. Thanks for the help.

8

After updating python-sql and applying the patch, my full query was completed in 0.3 seconds.

1 Like
9

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