openerp-expert-framework team mailing list archive

[Ferdinand Gassauer <office@xxxxxxxxxx>]

Am Mittwoch 03 Februar 2010 09:29:10 schrieb P. Christeas:
> On Wednesday 03 February 2010, you wrote:
> > Blueprint changed by Xavier (Open ERP):
> >   2. Though it's not quite ideal (understatement of the day), the
> >   `NUMERIC` postgres type...
> 
> Are we sure all this will not introduce a performance issue on the server?
> 
> AFAIK, postgres suggests that NUMERIC is slower than float. Yes, float is
>  bound to have rounding errors (actually noticable if we bring it to less
>  than 2 decimal places from the precision we want) [1], but it's much
>  faster.
> 
> Could we write a dummy python (standalone) program and test performance
>  with SQL's numeric, float, and python's Decimal etc?
> 
> We always have to consider the exponential[2] nature of un-optimizations:
> perhaps a number calculation is only a few μsecs, but when we use SUMs of
> those fields, and then also repeat [3] those sums a few hundred times, the
>  cost of a calculation will be much wasted time.
> 
> [1] I mean, if we want NUMERIC(12,4), and float can have 6 decimal places
>  for the range we use, then float maps ok. If float drops down to 4, we
>  could introduce a +-1 digit difference (actually mathematically correct
>  for non- trivial calculations). If, again, we only use NUMERIC(12,2) for
>  2-decimal money (like Eur), numeric already calcs wrong (eg. for VAT).
> 
> [2] The time needed for an operation is the product of all these factors
>  like SQL queries, ORM code, Python representation layers, high-level code
>  (like report loops) etc. Each time we put another abstraction layer, we
>  add one factor.
> 
> [3] There is many unfortunate python loops in the code, which keep querying
> the SQL for results, separately per orm row.
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~openerp-expert-framework
> Post to     : openerp-expert-framework@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~openerp-expert-framework
> More help   : https://help.launchpad.net/ListHelp
> 
for compliance with accounting / audit IMHO we should stick to postgres 
numeric - even if it looks more expensive - but takes away the stress of 
programmers to deal with inevitable rounding differences using float (IMHO 
regardless of the number of digits used).
sum(amount)
sum(subsums(amount))
must return the same totals .... (think of a very structured chart of 
accounts)

Just checked a table with 104000 records (probably all in memory after first 
read which takes ~2 sec, but this is good because it gives an idea of the 
overhead of ~50% numeric : integer )
select sum(numeric_field) => 173ms
select sum(integer) => 137ms
select count(*) => 90ms

unfortunately no float field

External auditors tend to ask how data are stored.

ad (3) - I agree there are optimizations possible

-- 
Best Regards

ChriCar Beteiligungs- und Beratungs- GmbH
http://www.chricar.at/ChriCar/index.html
Dr. Ferdinand Gassauer
Official Tiny Partner