c2c-oerpscenario team mailing list archive

["Saz\(OpenERP\)" <571203@xxxxxxxxxxxxxxxxxx>]

Let the team will decide.

** Also affects: openobject-addons/5.0
   Importance: Undecided
       Status: New

** Also affects: openobject-addons/trunk
   Importance: Undecided
     Assignee: Anup (OpenERP) (ach-openerp)
       Status: Confirmed

** Changed in: openobject-addons/5.0
       Status: New => Won't Fix

** Changed in: openobject-addons/trunk
   Importance: Undecided => Wishlist

** Changed in: openobject-addons/trunk
       Status: Confirmed => Triaged

** Changed in: openobject-addons/trunk
     Assignee: Anup (OpenERP) (ach-openerp) => OpenERP R&D Addons Team 2 (openerp-dev-addons2)

-- 
You received this bug notification because you are a member of C2C
OERPScenario, which is subscribed to the OpenERP Project Group.
https://bugs.launchpad.net/bugs/571203

Title:
  performance issue in stock/product.py

Status in OpenObject Addons Modules:
  Triaged
Status in OpenObject Addons 5.0 series:
  Won't Fix
Status in OpenObject Addons trunk series:
  Triaged

Bug description:
  no need to read the table twice - once for in once for out

        if 'in' in what:
            # all moves from a location out of the set to a location in the set
            cr.execute(
                'select sum(product_qty), product_id, product_uom '\
                'from stock_move '\
                'where location_id not in ('+location_ids_str+') '\
                'and location_dest_id in ('+location_ids_str+') '\
                'and product_id in ('+prod_ids_str+') '\
                'and state in ('+states_str+') '+ (date_str and 'and '+date_str+' ' or '') +''\
                'group by product_id,product_uom'
            )
            results = cr.fetchall()
        if 'out' in what:
            # all moves from a location in the set to a location out of the set
            cr.execute(
                'select sum(product_qty), product_id, product_uom '\
                'from stock_move '\
                'where location_id in ('+location_ids_str+') '\
                'and location_dest_id not in ('+location_ids_str+') '\
                'and product_id in ('+prod_ids_str+') '\
                'and state in ('+states_str+') '+ (date_str and 'and '+date_str+' ' or '') + ''\
                'group by product_id,product_uom'
            )
            results2 = cr.fetchall()


for a similar task I use this

            cr.execute(
                    'select sum('\
                    'case when location_dest_id in ('+location_ids_str+') then  product_qty else 0 end + '\
                    'case when location_id      in ('+location_ids_str+') then -product_qty else 0 end '\
                    ') as qty, ' \
                    'sum('\
                    'case when location_dest_id in ('+location_ids_str+') then  move_value_cost else 0 end + '\
                    'case when location_id      in ('+location_ids_str+') then -move_value_cost else 0 end '\
                    ') as value_cost, product_id '\
                    'from stock_move '\
                    'where (location_id  in ('+location_ids_str+') '\
                    'or location_dest_id in ('+location_ids_str+')) '\
                    'and product_id in ('+prod_ids_str+') '\
                    'and state in ('+states_str+') '+ (date_str and 'and '+date_str+' ' or '') +''\
                    'group by product_id'
                )


b5-proposed=# explain select * from stock_move where location_id in (1,2,3) or location_dest_id in (1,2,3);
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on stock_move  (cost=25.79..114.51 rows=37 width=384)
   Recheck Cond: ((location_id = ANY ('{1,2,3}'::integer[])) OR (location_dest_id = ANY ('{1,2,3}'::integer[])))
   ->  BitmapOr  (cost=25.79..25.79 rows=37 width=0)
         ->  Bitmap Index Scan on stock_move_location_id_location_dest_id_product_id_state  (cost=0.00..12.89 rows=19 width=0)
               Index Cond: (location_id = ANY ('{1,2,3}'::integer[]))
         ->  Bitmap Index Scan on chircar_location_dest_id  (cost=0.00..12.89 rows=18 width=0)
               Index Cond: (location_dest_id = ANY ('{1,2,3}'::integer[]))
(7 rows)

b5 - current=# explain select * from stock_move where location_id in (1,2,3) or location_dest_id not in (1,2,3);
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------                                                                                                   
 Seq Scan on stock_move  (cost=0.00..183.63 rows=1675 width=384)                                                                                                                                               
   Filter: ((location_id = ANY ('{1,2,3}'::integer[])) OR (location_dest_id <> ALL ('{1,2,3}'::integer[])))                                                                                                    
(2 rows)