Create payment line combo box for entry line is very slow

[Colin Newell]

If you go to Payment Orders, Create Payment, then go to Create Payment Line and activate the combo box to select a payment on the entry line entry a very innefficient query is executed. On a live system we are using this takes 2-3 minutes to run. This is 2-3 minutes to select every single entry to pay which is debilitating.

The query being emitted that is so painful is this,

  SELECT id
  FROM account_move_line l
  WHERE account_id IN (
      select id
      FROM account_account
      WHERE type='payable' AND active
    )
    AND reconcile_id IS null
    AND credit > 0
    AND (
      SELECT
        CASE
          WHEN l.amount_currency < 0
          THEN - l.amount_currency
          ELSE l.credit
          END - coalesce(sum(pl.amount_currency), 0)
      FROM payment_line pl
        INNER JOIN payment_order po ON (pl.order_id = po.id)
      WHERE move_line_id = l.id
        AND po.state != 'cancel'
    ) > 0
    AND l.state <> 'draft'
    AND l.period_id IN (
      SELECT id
      FROM account_period
      WHERE fiscalyear_id IN (2)
    )

The code that is generating this comes from two places,

addons/account_payment/account_move_line.py in the method _to_pay_search
addons/account/account_move_line.py in the method _query_get

By altering the query to pull the account_period query out I was able to make the query run an order of magnitude faster. With Postgres this was possible,

with periods as (
    SELECT id
    FROM account_period
    WHERE fiscalyear_id IN (2)
)

  SELECT id
  FROM account_move_line l
  WHERE
    l.state <> 'draft'
    AND reconcile_id IS null
    AND credit > 0
  AND account_id IN (
      select id
      FROM account_account
      WHERE type='payable' AND active
    )
    AND (
      SELECT
        CASE
          WHEN l.amount_currency < 0
          THEN - l.amount_currency
          ELSE l.credit
          END - coalesce(sum(pl.amount_currency), 0)
      FROM payment_line pl
        INNER JOIN payment_order po ON (pl.order_id = po.id)
      WHERE move_line_id = l.id
        AND po.state != 'cancel'
    ) > 0
    AND l.period_id IN (select id from periods)

To prove this had a practical positive effect I came up with a really dodgy hack for OpenERP that optimised the query in that situation. Assuming you have a reasonable size dataset this should prove that a performance optimisation is worth performing. I'm not suggesting that you should use this patch however as it's obviously very brittle and generally not a good idea.

[Twinkle Christian(OpenERP) (tch-openerp)]

Hello Colin,

Are you still facing this issue ? Please provide your notification for this .

Thanks

[Colin Newell (colin-newell)]

Yes.

[Colin Newell (colin-newell)]

I notice that you have marked this as incomplete - "Cannot be verified, the reporter needs to give more info."

What information do you need?

[Olivier Dony (Odoo) (odo-openerp)]

Hi Colin,

I have reproduced this behavior on the customer's database and will be investigating it soon. You don't need to provide any additional info at this point.

Thanks,