openerp-expert-framework team mailing list archive

[Ferdinand Gassauer <office@xxxxxxxxxx>]

Hello!
To continue the discussion of
https://bugs.launchpad.net/bugs/850752

******** Quote ***********
Olivier Dony (OpenERP) (odo-openerp) wrote on 2011-09-15:
1.- Not many function fields can have a proper fnct_search method. In many 
cases, the only way to implement it would be to compute the value of the 
function field for all database records, and then search among these for a 
match. This is not only very inefficient, but also dangerous in terms of 
performance, and cannot be allowed. On top of it, this approach requires 
implementing many search operators (like, ilike, =, !=, >, <) in pure python 
(for searching among the computed results in memory) which would be stupid: 
the database is the right place for this.
******** End Quote ***********

Some aspects:
filter/sort of function fields
* the fnct_search should only be used in rare cases - I agree - if the filter 
does not concern the result but  alters the computation of the result.  
* the filter (like, ilike, =, !=, >, <) on results and ordering the results 
should be trivial to implement 

>  IMHO there is no reason why filters can not be applied to results
>  (appart that it is not coded)
>  the "simple" rule
>  domain
>  * is DB field - add condition to SQL WHERE query
>  * else filter results.
>  would do it.

Performance:
there are many ways to look at "performance"
-- technical issues
* postgres query time
* python execution time
* python memory consumption
* communication
** number of SQL statements
** number of RPC calls
** qty to transfer ( line speed, costs)
-- IMHO we could agree that until here we should not spend more then 1-2 
seconds (A)
* necessary user interactions + time  to get the desired result - and here is 
the problem !!!!
at the end of the day it's this overall performance (B) which counts. 

The argument 
"because a required functionality destroys the performance (A) we do not offer 
it"
translated from quote above
(This is not only very inefficient, but also dangerous in terms of 
performance, and cannot be allowed.)
can not be accepted as the company requests do not disappear. 

Obviously - offering SAAS OpenERP itself does not encounter these problems, 
but all partners must deal with these issues in pre-sales discussion with 
prospects. 

All those making installations without averting a prospect of theses problems 
harm  their own image of being a credible and competent partner and the image 
of OpenERP itself as most advanced opensource ERP. 
see comment 6 of Raphael of bug above 

Sometimes workarounds can be found  to increase performance (B) issues like

* turning off auto search on big tables - it's nonsense to display the first 
1-5% or so of data by default - "if the user has to enter search criteria"
** may be this should also work for o2m/m2m fields if no search string is 
specified ??

* override m2o to display only relevant data 

* change default buttons to display only hot / "open" resources (mostly done 
in v6) or create multi state buttons ( for invoices draft,open,proforma) 

* eliminate dashboards - addons-no-fluff branch

* store function fields (which is mostly not a solution due to the different 
context variables in the function)

* alter default sort to display the most wanted (newest/hot/alphabetical) on 
top
** this is why I argue 
*** for Koo's feature to remember the last sort per user
*** for new feature to specify sort order in XML files which overrides the 
default order in py

* alter the column position to display the more relevant at the right of the 
window to avoid scrolling (manual reconciliation in v60 does not display the 
most important fields debit/credit for reconciliation on my laptop, have to 
scroll )
** Koo remembers the column position per user 

* create exactly  the functions which fall into the category "This is not only 
very inefficient, but also dangerous in terms of performance, and cannot be 
allowed" 
** Example is 
************ Quote *******
> 2) the wizard that opens inventory for a location could have an
> option to show only available products * this is what I have done for
> v5 already - should be come standard and default in many wizards
Excellent, could you perhaps provide a minimalistic merge proposal for
adding that to trunk, in order to make it available in standard addons?
************ End Quote ********
I do not see a performance difference between having a domain filter on qty 
and coding this in the wizard because the code acts always on the computed 
function field. it is just much more costly to solve such issues for every 
table. 

Data Access
in most installations an ABC analysis of used data will show that usage of A 
items will have a significant impact, that's why I am arguing for materialized 
views to reduce the number of records which have to be accessed to compute the 
most frequent values (balances, qty) NOT  from journals  like 
account_move_line, analytic line, stock_journal.

IIRC all these arguments have been turned down in the past, because of the 
proofed argument the accessing the necessary data is fast enough. (Fabien).
so let it be for now, it's easy to detect access problems with tools like
http://epqa.projects.postgresql.org/
and creating missing indexes solved most performance problems in the past 
indeed.

To get an idea of performance B issues set up a more realistic scenario.
physical or virtual
postgres db server
openerpserver 
* connects via 1GB ehternet to postgres server 
client connection
** 1 GB inhouse
** 1-4 MBit extern
** ADSL extern 
typical clients screen size (large for desktop, medium for laptop) with 
reasonable fontsize 

-- 
ferdinand