openerp-india team mailing list archive

["Fabien \(Open ERP\)" <fp@xxxxxxxxxxx>]

released in r6582.

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

-- 
You received this bug notification because you are a member of OpenERP
Indian Team, which is subscribed to OpenERP Addons.
https://bugs.launchpad.net/bugs/571203

Title:
  performance issue in stock/product.py

Status in OpenERP Addons (modules):
  Fix Released
Status in OpenERP Addons 5.0 series:
  Won't Fix
Status in OpenERP Addons trunk series:
  Fix Released

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)

To manage notifications about this bug go to:
https://bugs.launchpad.net/openobject-addons/+bug/571203/+subscriptions