Slow to list Lines to Pay when have lot of data pending to reconcile
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
Files
Download | Creator | Timestamp | Type |
---|---|---|---|
issue7129.diff | @resteve | 2018-02-12 14:51:51.992000 UTC | text/plain |