openerp-india team mailing list archive

[Kyle Waid <1086872@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

ORM generates inefficient SQL queries for use in advanced filters. Use stock.picking as example. I have filter buttons at the top. 
Ready, Waiting, Backorder, etc. Additionally I have search view fields like origin, partner, reference. When I use these filters and fields it narrows down the results for me.

Now lets consider advanced filters. When I select an advanced filter and
search, the ORM queries the database for all records matching the
textual search with ilike. It does a query with only the advanced
filter.  It does not consider the filters I previously selected. Here is
a full example


I have 1 million stock.picking records.
I filter the records based on available and waiting status. This narrows down the results from 1 million to 100.
I then want to filter those records even further by creating an advanced filter. Instead of filtering the remaining results, it does a textual search based on the entire table, not what I already have filtered. After it fetches these results, it again queries the database to filter based on my results. This is extremely inefficient.

IMO this feature needs to be refactored.
Some more context. I am trying to create a feature that allows a user to search pickings that contain specific products. In this database there are 3.5 million stock moves. By using an advanced filter it hits the database by searching the entire table every time regardless of any filters already in place. It could take over 1 minute to return a result, but if it used the previously filtered conditions it would only take a few seconds.

Also consider the user has already filtered results, so what good does
it do to search records that will never be returned.

** Affects: openobject-addons
     Importance: Undecided
         Status: New

-- 
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/1086872

Title:
  ORM Search advanced filters inefficient

Status in OpenERP Addons (modules):
  New

Bug description:
  ORM generates inefficient SQL queries for use in advanced filters. Use stock.picking as example. I have filter buttons at the top. 
  Ready, Waiting, Backorder, etc. Additionally I have search view fields like origin, partner, reference. When I use these filters and fields it narrows down the results for me.

  Now lets consider advanced filters. When I select an advanced filter
  and search, the ORM queries the database for all records matching the
  textual search with ilike. It does a query with only the advanced
  filter.  It does not consider the filters I previously selected. Here
  is a full example

  
  I have 1 million stock.picking records.
  I filter the records based on available and waiting status. This narrows down the results from 1 million to 100.
  I then want to filter those records even further by creating an advanced filter. Instead of filtering the remaining results, it does a textual search based on the entire table, not what I already have filtered. After it fetches these results, it again queries the database to filter based on my results. This is extremely inefficient.

  IMO this feature needs to be refactored.
  Some more context. I am trying to create a feature that allows a user to search pickings that contain specific products. In this database there are 3.5 million stock moves. By using an advanced filter it hits the database by searching the entire table every time regardless of any filters already in place. It could take over 1 minute to return a result, but if it used the previously filtered conditions it would only take a few seconds.

  Also consider the user has already filtered results, so what good does
  it do to search records that will never be returned.

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