Merge lp:~alhashash/openobject-addons/trunk-faster-move-line-reconcile into lp:openobject-addons

[Mohammad Alhashash]

When reconciling or updating large number of account move lines the process is extremely slow. Profiling shows that the bottleneck is account.invoice._get_invoice_from_reconcile() which triggers update of stored fields in related invoices. This functions uses the ORM to traverse move line and reconciliation records and to serach for invoices.

This patch uses SQL directly and bypasses the ORM in account.invoice._get_invoice_from_line() and account.invoice._get_invoice_from_reconcile() for up to two orders of magnitude faster reconcile and update operation for large number of move lines.

[lp:~alhashash/openobject-addons/trunk-faster-move-line-reconcile]

9457. By Mohammad Alhashash on 2014-05-14

Fix typos and add comment.

9458. By Mohammad Alhashash on 2014-05-14

Enhance SQL query

Unmerged revisions

9458. By Mohammad Alhashash on 2014-05-14

Enhance SQL query

9457. By Mohammad Alhashash on 2014-05-14

Fix typos and add comment.

9456. By Mohammad Alhashash on 2014-05-13

Use SQL directly and bypass the ORM in account.invoice._get_invoice_from_line() and account.invoice._get_invoice_from_reconcile() for much faster account_move_line reconcilliation and update. Up to two orders of magnitude faster when updating/reconciling a lot of move lines.

=== modified file 'account/account_invoice.py'
--- account/account_invoice.py	2014-05-07 17:01:12 +0000
+++ account/account_invoice.py	2014-05-14 07:07:53 +0000
@@ -187,30 +187,34 @@
         return result
 
     def _get_invoice_from_line(self, cr, uid, ids, context=None):
-        move = {}
-        for line in self.pool.get('account.move.line').browse(cr, uid, ids, context=context):
-            if line.reconcile_partial_id:
-                for line2 in line.reconcile_partial_id.line_partial_ids:
-                    move[line2.move_id.id] = True
-            if line.reconcile_id:
-                for line2 in line.reconcile_id.line_id:
-                    move[line2.move_id.id] = True
-        invoice_ids = []
-        if move:
-            invoice_ids = self.pool.get('account.invoice').search(cr, uid, [('move_id','in',move.keys())], context=context)
+        '''Returns all invoices related to move lines (ids) or related to any reconciled move lines'''
+        query = '''
+        SELECT i.id
+            FROM account_move_line l
+                 INNER JOIN
+                    (   SELECT coalesce(reconcile_id, reconcile_partial_id) id
+                          FROM account_move_line
+                         WHERE id IN %s AND (reconcile_id IS NOT NULL OR reconcile_partial_id IS NOT NULL)
+                      GROUP BY coalesce(reconcile_id, reconcile_partial_id)
+                    ) r
+                    ON r.id = l.reconcile_id OR r.id = l.reconcile_partial_id
+                 INNER JOIN account_invoice i ON i.move_id = l.move_id
+        GROUP BY i.id;
+        '''
+        cr.execute(query, (tuple(ids),))
+        invoice_ids = [invoice_id for (invoice_id,) in cr.fetchall()]
         return invoice_ids
 
     def _get_invoice_from_reconcile(self, cr, uid, ids, context=None):
-        move = {}
-        for r in self.pool.get('account.move.reconcile').browse(cr, uid, ids, context=context):
-            for line in r.line_partial_ids:
-                move[line.move_id.id] = True
-            for line in r.line_id:
-                move[line.move_id.id] = True
-
-        invoice_ids = []
-        if move:
-            invoice_ids = self.pool.get('account.invoice').search(cr, uid, [('move_id','in',move.keys())], context=context)
+        '''Returns all invoices related to move lines reconciled using ids'''
+        query = '''
+        SELECT i.id
+            FROM account_move_line l JOIN account_invoice i ON i.move_id = l.move_id
+           WHERE l.reconcile_id IN %(ids)s OR l.reconcile_partial_id IN %(ids)s
+        GROUP BY i.id
+        '''
+        cr.execute(query, {'ids': tuple(ids)})
+        invoice_ids = [invoice_id for (invoice_id,) in cr.fetchall()]
         return invoice_ids
 
     _name = "account.invoice"