Tryton - Issues

 

Issue7129

Title Slow to list Lines to Pay when have lot of data pending to reconcile
Priority feature Status chatting
Superseder Nosy List ced, pokoli, resteve
Type feature request Components account_payment
Assigned To Keywords patch
Reviews

Created on 2018-02-12.15:07:47 by resteve, last changed by ced.

Files
File name Uploaded Type Edit Remove
issue7129.diff resteve, 2018-02-12.15:51:51 text/plain
Messages
msg38326 (view) Author: [hidden] (ced) (Tryton committer) (Tryton translator) Date: 2018-02-12.16:11:21
The proposal is wrong because search_payment_amount should work for all clauses. It is not only related to search of payment_amount greater than 0. Just like get_payment_amount returns also a value for reconciled line.
Also it will be clearer if you provide the analyze of the query.
msg38325 (view) Author: [hidden] (pokoli) (Tryton committer) (Tryton translator) Date: 2018-02-12.16:07:44
Don't understand what do you mean with such patch. 

Could you please explain what are you trying to improve? 

Here is the query is run on the "Lines to pay" action window. 

SELECT "a"."id" AS "id", "a"."payment_blocked" AS "payment_blocked", "a"."create_date" AS "create_date",
"a"."move" AS "move", "a"."create_uid" AS "create_uid",
"a"."amount_second_currency" AS "amount_second_currency", "a"."state" AS "state",
"a"."debit" AS "debit", "a"."party" AS "party", "a"."description" AS "description",
"a"."reconciliation" AS "reconciliation", "a"."write_date" AS "write_date",
"a"."second_currency" AS "second_currency", "a"."write_uid" AS "write_uid",
"a"."payment_direct_debit" AS "payment_direct_debit", "a"."account" AS "account",
"a"."maturity_date" AS "maturity_date", "a"."credit" AS "credit",
CAST(EXTRACT('EPOCH' FROM COALESCE("a"."write_date", "a"."create_date")) AS VARCHAR) AS "_timestamp"
FROM "account_move_line" AS "a"
LEFT JOIN "party_party" AS "b" ON ("b"."id" = "a"."party")
LEFT JOIN "account_account" AS "c" ON ("c"."id" = "a"."account")
LEFT JOIN "account_move" AS "d" ON ("d"."id" = "a"."move")
WHERE ((("a"."payment_blocked" = false) OR ("a"."payment_blocked" IS NULL))
    AND (("c"."kind" IN ('payable', 'receivable'))
        AND ("c"."active" IN (True, False)) AND (((("c"."company" IN (1))
                    AND ("c"."active" = True))) AND ("c"."active" = True)))
    AND ("a"."party" IS NOT NULL) AND ("a"."reconciliation" IS NULL)
    AND (("a"."id" IN (SELECT "e"."id"
                FROM "account_move_line" AS "e"
                LEFT JOIN "account_payment" AS "f" ON (("e"."id" = "f"."line")
                    AND ("f"."state" != 'failed'))
                INNER JOIN "account_account" AS "g" ON ("e"."account" = "g"."id")
                WHERE ("g"."kind" IN ('payable', 'receivable'))
                GROUP BY "e"."id", "g"."kind", "e"."second_currency"
                HAVING (CASE WHEN ("e"."second_currency" IS NULL) THEN
                    (ABS(("e"."credit" - "e"."debit")) - SUM(COALESCE("f"."amount", 0)))
                ELSE (ABS("e"."amount_second_currency") - SUM(COALESCE("f"."amount", 0))) END != 0))))
                    AND ((("d"."state" = 'posted'))) AND (("a"."debit" > 0) OR ("a"."credit" < 0)) AND
                    (("b"."id" IN (SELECT "h"."party" FROM "account_payment_sepa_mandate" AS "h" WHERE ("h"."party" IS NOT NULL))) AND ("b"."active" IN (True, False)))) 
                LIMIT 1000;

I uploaded a explain anaylize of the query on https://explain.depesz.com/s/rqbu which reveals that most of the time is spent on computing the having clause. 

Could you run the explain analyze on your database and upload the result to https://explain.depesz.com? If you don't want to upload could you give some information of your database size: Which number of records do you have on the account_move_line table? How much are unreconciled? How much are of receivable/payable?
msg38324 (view) Author: [hidden] (ced) (Tryton committer) (Tryton translator) Date: 2018-02-12.16:06:28
Please follow: http://www.tryton.org/how-to-contribute.html
msg38323 (view) Author: [hidden] (resteve) Date: 2018-02-12.15:51:51
Query1:

('SELECT "a"."id" FROM "account_move_line" AS "a" LEFT JOIN "account_payment" AS "b" ON (("a"."id" = "b"."line") AND ("b"."state" != %s)) INNER JOIN "account_account" AS "c" ON ("a"."account" = "c"."id") WHERE (("c"."kind" IN (%s, %s)) AND ("a"."reconciliation" IS NULL)) GROUP BY "a"."id", "c"."kind", "a"."second_currency" HAVING (CASE WHEN ("a"."second_currency" IS NULL) THEN (ABS(("a"."credit" - "a"."debit")) - SUM(COALESCE("b"."amount", %s))) ELSE (ABS("a"."amount_second_currency") - SUM(COALESCE("b"."amount", %s))) END != %s)', ('failed', 'payable', 'receivable', 0, 0, 0))

Query 2:

('SELECT %s AS "amount" FROM "account_move_line" AS "a" WHERE (("a"."reconciliation" IS NULL) AND (%s != %s))', (0, 0, 0))

Union query1 and query 2:

('SELECT "a"."id" FROM "account_move_line" AS "a" LEFT JOIN "account_payment" AS "b" ON (("a"."id" = "b"."line") AND ("b"."state" != %s)) INNER JOIN "account_account" AS "c" ON ("a"."account" = "c"."id") WHERE (("c"."kind" IN (%s, %s)) AND ("a"."reconciliation" IS NULL)) GROUP BY "a"."id", "c"."kind", "a"."second_currency" HAVING (CASE WHEN ("a"."second_currency" IS NULL) THEN (ABS(("a"."credit" - "a"."debit")) - SUM(COALESCE("b"."amount", %s))) ELSE (ABS("a"."amount_second_currency") - SUM(COALESCE("b"."amount", %s))) END != %s) UNION ALL SELECT %s AS "amount" FROM "account_move_line" AS "a" WHERE (("a"."reconciliation" IS NULL) AND (%s != %s))', ('failed', 'payable', 'receivable', 0, 0, 0, 0, 0, 0))

See issue7129.diff attachment
msg38322 (view) Author: [hidden] (pokoli) (Tryton committer) (Tryton translator) Date: 2018-02-12.15:41:00
This domain is already applied on the action window: 

http://hg.tryton.org/modules/account_payment/file/tip/account.xml#l29
msg38321 (view) Author: [hidden] (resteve) Date: 2018-02-12.15:07:47
When there are lot of records pending to be reconciled (example 5300 records), it is very slow to list them in menĂº "Lines to Pay".

The SQL:

SELECT COUNT(1) 
	FROM "account_move_line" AS "a" 
	LEFT JOIN "account_account" AS "b" ON ("b"."id" = "a"."account") 
WHERE (((
        ("b"."company" = 1) AND
	("b"."kind" IN ('receivable', 'payable')) AND 
	("b"."active" IN (true, false)) AND
	(((("b"."company" IN (1)) AND ("b"."active" = true))) AND ("b"."active" = true))) 
	AND ("a"."party" IS NOT NULL) AND ("a"."reconciliation" IS NULL) AND
        (("a"."id" IN (
		SELECT "c"."id" 
			FROM "account_move_line" AS "c" 
			LEFT JOIN "account_payment" AS "d" ON (("c"."id" = "d"."line") AND ("d"."state" != 'failed')) 
			INNER JOIN "account_account" AS "e" ON ("c"."account" = "e"."id") 
		WHERE ("e"."kind" IN ('payable', 'receivable')) 
		GROUP BY "c"."id", "e"."kind", "c"."second_currency" 
		HAVING (CASE WHEN ("c"."second_currency" IS NULL) THEN 
			(ABS(("c"."credit" - "c"."debit")) - SUM(COALESCE("d"."amount", 0))) ELSE 
			(ABS("c"."amount_second_currency") - SUM(COALESCE("d"."amount", 0))) END != 0)
		))) AND (("a"."credit" > 0) OR ("a"."debit" < 0))) AND (((("b"."company" IN (1)) AND ("b"."active" IN (true, false)) AND (((("b"."company" IN (1)) AND ("b"."active" = true))) AND ("b"."active" = true))))))

We improve with next line:

AND c.reconciliation IS NULL

after:

WHERE ("e"."kind" IN ('payable', 'receivable'))

because a lot of the time is lost with JOIN + GROUP BY + HAVING
History
Date User Action Args
2018-02-12 16:11:21cedsetmessages: + msg38326
2018-02-12 16:07:44pokolisetmessages: + msg38325
2018-02-12 16:06:28cedsetnosy: + ced
messages: + msg38324
2018-02-12 15:51:52restevesetfiles: + issue7129.diff
messages: + msg38323
keyword: + patch
2018-02-12 15:41:00pokolisetnosy: + pokoli
messages: + msg38322
2018-02-12 15:07:47restevecreate

Showing 10 items. Show all history (warning: this could be VERY long)