Performance problem of BOM* functions.

Description

1. Need to ignore inactive BOM, i.e only M_Product_BOM.IsActive='Y'
2. Need to include basic check for infinite BOM, i.e M_Product_BOM.M_Product_ID != M_Product_BOM.M_ProductBOM_ID
3. For PostgreSQL, function should be declare as Stable instead of Volatile.

Environment

None

Activity

Show:
Carlos Ruiz
May 21, 2014, 6:37 PM

Adding some documentation about commit http://bitbucket.org/idempiere/idempiere/commits/df38654

POSTGRESQL has three types of functions:
http://www.postgresql.org/docs/9.3/static/sql-createfunction.html
volatile (the default)
stable (the return for same parameter is the same within a trx)
immutable (the return is always the same)
so, changing the postgresql functions to stable speed up queries because the return value is cached

ORACLE:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/create_function.htm#LNPLS01370
deterministic (similar to postgresql immutable)
NOTE that oracle doesn't have a notation for the stable - so this cannot be exploited directly within the function.
However you can workaround it using subquery caching as explained here:
http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html
so, in the info window, you can change the info column definition from:

to

other candidates for change to stable:
acctBalance
bpartnerRemitLocation
currencyBase
currencyConvert
currencyRate
currencyRound
documentNo
get1099bucket
get_Sysconfig
invoiceDiscount
invoiceopen
InvoiceopenToDate
invoicePaid
InvoicepaidToDate
nextbusinessday
paymentAllocated
paymentAvailable
paymenttermDiscount
paymenttermduedate
paymenttermDueDays
prodqtyordered
prodqtyreserved
ProductAttribute

candidates for change to immutable:
addDays
add_months
charAt
daysBetween
firstOf
round
subtractdays
trunc

will keep this ticket open to do the suggested changes on these functions

Assignee

Heng Sin Low

Reporter

Heng Sin Low

Labels

Tested By

None

Components

Priority

Major
Configure