Funciones BomPriceXXX y BomQtyXXX, dudas y comentarios

Todo lo que desee comentar, no relacionado con soporte, el área social. Se permiten las críticas, quejas. De todas formas, por favor mantener el tono de respeto con los demás.
Forum rules
No hay censura ni gobierno aquí, solo libertad.

Funciones BomPriceXXX y BomQtyXXX, dudas y comentarios

Postby javAd » Thu Jun 09, 2011 7:40 pm

Buenas, como puse en este otro thread viewtopic.php?f=43&t=1297 , vengo de un sistema basado en Compiere pero que sigue usando PL/Java. Partiendo me puse a trasplantar sus funciones pl/pgsql (basado en un dump que esta en instalador para windows de la version 360LTS) y de a poco eliminar PL/Java (esto suponiendo que iba a traerme mejoras de peformance). Ok, el tema es que encontré varias cosas que tal vez le sirva a Adempiere/Idempiere:

1) primeros cambios de PL/Java a PL/Sql.
Lo primero que hice fue simplemente pisar la funcion bomPriceStd(numeric,numeric) definida usando Pl/java por la que tienen ustedes y correr la siguiente query (base Java = base identica pero con PL/Java)
Code: Select all
select p.M_product_id,p.Name, pp.M_PriceList_Version_ID,
BOMPriceStd(p.M_Product_ID,pp.M_PriceList_Version_ID)
from M_Product p inner join M_ProductPrice pp on
p.M_Product_id = pp.M_Product_ID


En la base con PL/Java me retornaba
2144 filas, 3500 mls

En la base con PL/PGSql

2144 filas, 6,875 segundos.

Extrañamente tardaba mas PL/PGSql..... Lo raro es que corriendo un query similar sobre la base de Adempiere andaba más rapida (algo asi como 1 mls por cada invocacion a BOMPriceStd). Entre a ejecutar VACUMS, reindixaciones, etc etc, suponiendo que mi base estaba poco optimizada, pero seguia todo igual...
Despues de un tiempo, me di cuenta que bajo Adempiere utilizan numeric(10) para ids; bajo mi sistema se usa integer (por lo tanto los indices son de este tipo). Lo que hice para saber si el problema venia por ahi fue tomar su bomPriceStd(nuermic,numeric,numeric) y trasladarlo a bomPriceStd(integer,integer,integer); de esta manera los tipos de los parametros machean exacto con los tipos de las columnas ID (y por lo tanto con los indices).
Ejecute esta nueva funcion y los resultados fueron
2144 filas, 1400 mls! (0,5 mls por bom!)

Buenisimo! Una mejora notable.
Ahroa lo que me empezo resultar raro es que mi query corria mejor que la de ustedes! (no mucho, pero algo mas rapida).
A esto le veo dos posibles razones:
-los indices sobre columnas integer son más eficientes que sobre numeric(10); esto es bastante probable.
y/o
-de igual manera que los indices basados en integer se ralentan cuando se los machea contra algo numeric; los indices basados en numeric(10) pueden que se ralenten cuando son macheados con algo numeric (numeric es de longitud variable; tal vez el optimizador de queries anda mejor si el match de tipos es exacto). Lo veo menos probable, pero si afecta podrian mejorar la performance de muchas de estas funciones (ya que todas reciben numeric con parametro de entrada); supongo que deberia ser tan simple como hacer un cast explicito de numeric a numeric(10) dentro de las funciones (supongo que a una variable local) y usar el nuevo dato como para los selects contra ids (la otra opcion seria redefinir los parametros directamente como numeric(10) , pero no si postgres permite esto).
No lo probe, pero en una de esas.....

2) PriceLImit y su semantica: desde el sistema del que vengo un precio limite igual a cero significa ilimitado; es tratado asi en adempiere? Si es asi, hay algo conceptualmente erroneo en las fuciones bomPriceXXX: todas estas funciones hacen inicialmente algo como
Code: Select all
   --   Try to get price from PriceList directly
   SELECT   COALESCE (SUM(PriceLimit), 0)
         INTO   v_Price
      FROM   M_ProductPrice
   WHERE M_PriceList_Version_ID=PriceList_Version_ID AND M_Product_ID=Product_ID;


   --   No Price - Check if BOM
   IF (v_Price = 0) THEN
       -- busqueda recuriva BOM


Ok, aca el tema al usar COALESCE, si no existe una entrada en M_ProductPrice v_Price toma 0 (esto hace que luego se intente el precio bom); ahora bien, si la entrada M_ProductPrice existe, pero tiene valor 0 (esto tiene importancia semantica para PriceLimit), se pasa en la misma situcaion! Esto es, si el precio fue explicatamente seteado a 0 se lo trata como si fuera BOM. Esto (agregado junto con lo que comento mas adelante acerca de BOM) ralenta las cosas, pero ademas puede ser incorrecto en el caso de PriceLImit (no hay forma de especificar precio limite a 0 en un producto BOM, salvo ponerle precio limite 0 a TODOS sus componentes....).

La solucion que veo es simple, sacar el COALESCE y modifcar el if por null ; algo asi

Code: Select all
   --   Try to get price from PriceList directly
   SELECT   SUM(PriceLimit)
         INTO   v_Price
      FROM   M_ProductPrice
   WHERE M_PriceList_Version_ID=PriceList_Version_ID AND M_Product_ID=Product_ID;


   --   Really No Price - Check if BOM
   IF (v_Price IS NULL) THEN
            vPrice := 0; -- OJO, antes hay que inicializar a 0
           -- busqueda recursiva BOM igual que antes
       
         


3) La busqueda recurisva BOM en BOMPriceXXX's esta haciendo algo mal (mas alla de que lo que puse acerca del IF)... O es incorrecta o es ineficiente (aunque la ineficiencia casi seguro que no se nota porque Postres optimiza un join innecesario); el codigo tiene la siguiente forma (que dicho sea de paso, es equivalente a lo que se hace en mi PL/Java; esto es, mi PL/Java comete el mismo error), tomando como ejemplo esta vez BOMPriceStd :

Code: Select all

      FOR bom IN 
         SELECT b.M_ProductBOM_ID, b.BOMQty, p.IsBOM
         FROM M_Product_BOM b, M_Product p
         WHERE b.M_ProductBOM_ID=p.M_Product_ID
           AND b.M_Product_ID=Product_ID
      LOOP
         v_ProductPrice := bomPriceStd (bom.M_ProductBOM_ID, PriceList_Version_ID);
         v_Price := v_Price + (bom.BOMQty * v_ProductPrice);
      END LOOP;


Ineficiente: la tabla P en el "inner join" es innecesaria; tendria sentido en el caso de que M_Product_BOM.M_ProductBOM_ID se permita que sea null o que se permitan valores inexistentes en M_Product.M_Product_ID, pero eso nunca se puede pasar (la columna esta definida como clave foranea a M_Product.M_Product_ID y como NOT NULL); esto es, lo anterior es equivalente a simplemente:

Code: Select all

      FOR bom IN 
         SELECT b.M_ProductBOM_ID, b.BOMQty
         FROM M_Product_BOM b
         WHERE b.M_Product_ID=Product_ID
      LOOP
         v_ProductPrice := bomPriceStd (bom.M_ProductBOM_ID, PriceList_Version_ID);
         v_Price := v_Price + (bom.BOMQty * v_ProductPrice);
      END LOOP;


Incorrecta: la tabla P parece ser necesaria porque hay que chequear que el producto "padre" realmente este marcado como IsBOM (esto es SI se hace en bomQtyXXX); el tema es que aún si este chequeo es necesario el JOIN y la tabla P siguen siendo extraños; se soluciona agregando un condicion previa o se chequea en el where que el producto padre es BOM (NO los componentes; eso lo hace la llamada recursiva); optanto por esta ultima forma (ya que de esta manera siempre se ejecutan una sola query en cualuqiera de los casos):

Code: Select all

      FOR bom IN 
         SELECT b.M_ProductBOM_ID, b.BOMQty
         FROM M_Product_BOM b
         WHERE
           b.M_Product_ID=Product_ID
                        AND -- is Product_ID really BOM?
                        EXISTS (SELECT * FROM M_Product where M_Product_ID = Product_ID AND IsBOM ='Y')
      LOOP
         v_ProductPrice := bomPriceStd (bom.M_ProductBOM_ID, PriceList_Version_ID);
         v_Price := v_Price + (bom.BOMQty * v_ProductPrice);
      END LOOP;

Fijense que aún asi no uso la tabla P, ni el join; solo agregue un EXISTS sobre el product actual.

Creo que de las dos alternativas, la segunda es la correcta ya que es compatible con lo que hacen las BomQtyXXX.


4) Cual es la semantica esperada de BOMQty(IdProductoExistente,IdWarehouseExistente,0)? Esto es, una invocación en la que se especifica el producto y el almacen, pero NO la Locacion (M_Locator_ID = 0). Mirando mi codigo en PL/Java (no chequie en sus versiones anteriores), es claro que si se especfica M_Locator_ID = 0, se esta solicitando la sumatoria de determinado producto en todas las locaciones dentro de un almacen; si se le especifica la locacion (M_Locator_ID <> 0); entoces se esta solicitando la cantidad SOLO en esa locacion (y dentro del almacen dado). Esto tiene sentido, pero es la semantica que usan en Adempiere?
La funcion en mi PL/Java que se encarga de esto hace lo siguietne :

Code: Select all
   static BigDecimal getStorageQty( int p_M_Product_ID,int M_Warehouse_ID,int p_M_Locator_ID,String p_what )
    throws SQLException {
        BigDecimal quantity = null;
        String     sql      = "SELECT SUM(" + p_what + ") "
        + "FROM M_Storage s " + "WHERE M_Product_ID=?";

        if( p_M_Locator_ID != 0 ) {
            sql += " AND s.M_Locator_ID=?";
        } else {
            sql += " AND EXISTS (SELECT * FROM M_Locator l WHERE s.M_Locator_ID=l.M_Locator_ID"
               + " AND l.M_Warehouse_ID=?)";
        }

        PreparedStatement pstmt = OpenXpertya.prepareStatement( sql );

        pstmt.setInt( 1,p_M_Product_ID );

        if( p_M_Locator_ID != 0 ) {
            pstmt.setInt( 2,p_M_Locator_ID );
        } else {
            pstmt.setInt( 2,M_Warehouse_ID );
        }

        ResultSet rs = pstmt.executeQuery();

        if( rs.next()) {
            quantity = rs.getBigDecimal( 1 );
        }

        rs.close();
        pstmt.close();

        // Not found

        if( quantity == null ) {
            return OpenXpertya.ZERO;
        }

        return quantity;
    }    // getStorageQty

Esto es, el se especifico la locacion (p_M_Locator_ID != 0), SOLO toma en cuenta los M_Storage bajo la misma; si no se especifico (p_M_Locator_ID == 0), toma la suma de todas las locaciones pertenecientes al alamcen.

Ok, el tema es que bajo pl/pgSql en Adempiere, exacatemente en el mismo punto en que bajo PL/Java se llama a getStorageQty, se ejecuta el siguiente fragmento:

Code: Select all

      --   Get ProductQty
      SELECT    COALESCE(SUM(QtyOnHand), 0)
        INTO   v_ProductQty
      FROM    M_STORAGE s
      WHERE M_Product_ID=Product_ID
        AND EXISTS (SELECT * FROM M_LOCATOR l WHERE s.M_Locator_ID=l.M_Locator_ID
           AND l.M_Warehouse_ID=myWarehouse_ID);
      --
   --   DBMS_OUTPUT.PUT_LINE('Qty=' || v_ProductQty);
      RETURN v_ProductQty;



Esto es, nunca tienen en cuenta si se paso o no la locacion y usan la segunda condición que esta en PL/Java; esto es, siempre retornan la cantidad que hay en total en el almacen sin importar la locacion (en este sentido, estan usando el parametro locator_id para inferir en que almacen hay que buscar si este no se especifico).

Dicho esto; la semantica de Adempiere actual, es la esperada o hay un bug? Tal como esta, no hay forma de saber mediante estas funciones la cantidad de determinado articulo en una locacion especifica, ya que pl/pgsql siempre retorna la sumatioria.


5) Finalmente.... en Adempiere no existe la posibilidad de asociar un precio a un instancia de productos; eso si se puede en mi sistema (existe una tabla M_ProductPriceInstance que guarda esta info y funciones BOMPrice que toman como parametro el id de ASI); inversamente, ustedes tienen funciones para chequear el stock especifico de una instacia de producto, en mi sistema no (en realidad si a nivel de tablas, pero no existen la funciones BOMQtyXXXASI, solo las BOMQtyXXX)... Pensandolo un poco, tinen mucho más sentido su enfoque: por ej, una Camara digital modelo XYZ de color Rojo, naturalmente tiene el mismo precio que la misma camara pero de color Azul; por el contrario, es importante saber si en stock existen o no camaras de determinado color, ya que la gente en general no pide cualquiera.... Tambien, si dos isntancias distintas requieren distintos precios, bueno, entocnes no son tan similares y se deberia modelar como dos productos distintos.

Todo esto lo planteo, porque no se si Adempiere en el pasado cambio de enfoque y se dejo de soportar precios sobre instancias de productos, o si por el contrario, mi sistema en algun momento agrego esta funcionalidad (surge de Compiere, pero de manera intermedia via OpenXpertya). Si es lo primero, caules fueron las razones? Si es lo segundo, pensaron en agregar la capacidad de soportar distintos precios para distintas instacias?

PD : nuevamente este thread, como el anterior, probablemente este mal ubicado...
Saludos
Javier Ader
javAd
 
Posts: 6
Joined: Sat May 28, 2011 5:28 am
Location: Bahía Blanca, Bs. Aries, Argentina

Re: Funciones BomPriceXXX y BomQtyXXX, dudas y comentarios

Postby globalqss » Sat Jun 11, 2011 12:23 am

Javier, gracias por tus contribuciones.

Voy a necesitar tiempo para digerir todas las sugerencias que haces sobre pl/pgsql .... queda en mis pendientes.

No conozco a fondo OpenXpertya, ellos hicieron el fork de Compiere mucho antes que nosotros, supongo que ellos habrán hecho cambios que no hemos hecho en Adempiere y viceversa.

Por ejemplo, precio por instancia es algo que se ha planteado varias veces en los foros, pero en Adempiere no lo hemos hecho.

Que el precio límite cero sea ilimitado me suena también a un cambio de óptica de OpenXpertya vs Compiere.

Saludos,

Carlos Ruiz
globalqss
Senior
 
Posts: 599
Joined: Thu Dec 29, 2005 4:15 am
Location: Bogotá, Colombia

Re: Funciones BomPriceXXX y BomQtyXXX, dudas y comentarios

Postby javAd » Sat Jun 11, 2011 4:51 am

De nada, aunque en realidad Adempiere me esta contribuyendo más a mi que al revés...
En cuanto a lo que puse si jajaj muy largo; y bueno ya que estamos te dejo otro comentario, tal vez no sea un error, pero por las dudas:
los bomPriceXXX cuando pasan al calculo resursivo BOM toman la cantidad de los componente de M_Product_BOM pero los bomQtyXXX (en 3.6.0; en la otra versión que tengo, 3.4.2 no) cuando entra a calcular la parte BOM (no lo mire mucho , pero seguro que la idea es inferir las cantidad a partir de las cantidades de los componentes) uitliza otras tablas de prefijo PP (supongo que este es el nuevo esquema BOM al que te referias en el otro thread, porque en 3.4.2 direcamente no existen estas tablas); si mal no recuerdo PP_BOMLInes o algo por el estilo. Quiero decir, unas funciones toman la cantidad de los componentes de una tabla, y mientras que las otras de otra distinta (no necesariamente esta mal; mas que nada porque el precio BOM debería ser casi una sugerencia... a mi en lo particular no me gusta mucho ese precio automatico cuando no se da explícitamente...)
Saludos
Javier Ader
javAd
 
Posts: 6
Joined: Sat May 28, 2011 5:28 am
Location: Bahía Blanca, Bs. Aries, Argentina

Re: Funciones BomPriceXXX y BomQtyXXX, dudas y comentarios

Postby javAd » Sat Jun 11, 2011 8:08 am

----
Agrego otro detalle; muchas funciones (BOMQtyOnHand por ej) utilizan el sig. patron para setear variables locales
Code: Select all
   BEGIN
      SELECT   IsBOM, ProductType, IsStocked
         INTO   v_IsBOM, v_ProductType, v_IsStocked
      FROM M_PRODUCT
      WHERE M_Product_ID=Product_ID;
      --
   EXCEPTION   --   not found
      WHEN OTHERS THEN
         RETURN 0;
   END;


El tema es que la parte del "not found" es incorrecta; la excepción esperada por ej handler (NO_DATA_FOUND) no se dispara salvo que uno ponga el modificador STRICT: http://www.postgresql.org/docs/8.3/stat ... SQL-ONEROW (usando STRICT también puede que se dispare TOO_MANY_ROWS). Esto es, el codigo anterior debería ser

Code: Select all
   BEGIN
      SELECT   IsBOM, ProductType, IsStocked
         INTO STRICT   v_IsBOM, v_ProductType, v_IsStocked
      FROM M_PRODUCT
      WHERE M_Product_ID=Product_ID;
      --
   EXCEPTION   --   not found
      WHEN OTHERS THEN
         RETURN 0;
   END;


La otra alternativa, tal vez mas elegante es usar la variable especial FOUND (IF NOT FOUND return 0), como lo explica el link.
Me di cuenta cheuquendo la respuesta de por ej bomQtyOnHand(1,1,1) ; como id 1 no existe deberia retornar 0; pero me retorna 99999 (unlimited); esto bajo 3.4.2 (bajo 3.6, la verdad que no se, pero tambien incorrectamente se pasa a la parte que asume que isBOM).

Aclaro, por las dudas, que estoy usando Posgresql 8.3, el manual que linkie también es para esta versión (aunque no creo que esto haya cambiado en otras versiones). Lo que es casi seguro, es que todo esto viene porque hicieron el port de PL/Java a Oracle, y de ahi a PgSql. El tema es que estos SELECT INTO tienen distinta semantica dentro de pgSql; como aclara el link:

Note: The STRICT option matches the behavior of Oracle PL/SQL's SELECT INTO and related statements.

Esto es, el codigo tiene el sig. esperado si se les agrega el STRICT (todo esto seguro que también explica esos COALESCE(sum(X),0) o COALESCE(MAX(x),0) que en varios lados; como que hicieron esos "trucos" para asegurarse de que no se dispararian sobre Oracle... el tema es que bajo PostGreSQL no se disparan y si no me equivoco,simplemente setean a null, si no hay resultados).
Saludos
Javier Ader
javAd
 
Posts: 6
Joined: Sat May 28, 2011 5:28 am
Location: Bahía Blanca, Bs. Aries, Argentina


Return to Charla informal

Who is online

Users browsing this forum: No registered users and 1 guest

cron