Roy,

Yes, it is SQL mostly and DML sometimes, never DDL…

The fnCalculateComision was just an example and not a real function, but here's the code to the real one:

CREATE FUNCTION "fnInvExistenciaBodega" (IN "pIdProductoEmpresa" GUID COLLATE UNI, IN "pIdBodega" GUID COLLATE UNI, IN "pFechaMov" TIMESTAMP)
RETURNS DECIMAL(19,4)
BEGIN
  DECLARE Result DECIMAL(19,4) DEFAULT 0;

  EXECUTE IMMEDIATE '
     SELECT px.bodega AS existencia
     INTO ?
     FROM  inv_producto_existencia px
        INNER JOIN inv_bodega_ubicacion bu ON px.id_ubicacion = bu.id_ubicacion
     WHERE   px.id_producto_empresa  = ?
     AND     px.fecha  <= ?
     AND     bu.id_bodega = ?
     ORDER BY px.fecha desc
     RANGE 0 TO 1'
  USING Result, pIdProductoEmpresa, pFechaMov, pIdBodega;

  RETURN COALESCE(Result, 0.0);
END
VERSION 1.00!

This function is meant to return the available stock for and specific Item (pIdProductoEmpresa) on a specific Warehouse (pIdBodega) at a given time (pFechaMov). So if one would like to know the available stock for all items on a given warehouse, if possible with the following SELECT.

SELECT id_producto_empresa, fnInvExistenciaBodega(id_producto_empresa, '', CURRENT_TIMESTAMP) AS existencia
FROM inv_producto_empresa
WHERE activo = 'S'

Do you think there's way to overcome the preparation of the EXECUTE IMMEDIATE for each row?

Thanks for taking the time!

Regards,
Mario

Mario


It can be rewritten without the EXECUTE IMMEDIATE but wether it will be faster or not I don't know. You will need to test that. What you need to do before anything else is to ensure that the query you're using is as effective as you can make it. Run it as a simple query in EDBManager and use the execution plan to refine if necessary.

Just looking at your code 1) I don't know if the RANGE clause is necessary and 2) ditto for the ORDER BY clause. I suspect not from your description of what you are doing.

The function below could be totally wrong because I can't test, and I'm not the best with SQL on these NGs


CREATE FUNCTION "fnInvExistenciaBodega" (IN "pIdProductoEmpresa" GUID COLLATE UNI, IN "pIdBodega" GUID COLLATE UNI, IN "pFechaMov" TIMESTAMP)
RETURNS DECIMAL(19,4)
BEGIN
  DECLARE Result DECIMAL(19,4) DEFAULT 0;
  DECLARE DBCursor CURSOR FOR DBStmt;
  PREPARE DBStmt FROM
    'SELECT px.bodega AS existencia
     FROM  inv_producto_existencia px
        INNER JOIN inv_bodega_ubicacion bu ON px.id_ubicacion = bu.id_ubicacion
     WHERE   px.id_producto_empresa  = ?
     AND     px.fecha  <= ?
     AND     bu.id_bodega = ?';
 
    OPEN DBCursor USING Result, pIdProductoEmpresa, pFechaMov, pIdBodega;

    FETCH FIRST FROM DBCursor ('existencia') INTO Result;

  RETURN COALESCE(Result, 0.0);
END

If it works and it's still not good enough I'd recommend opening a support ticket with Tim.

Roy Lambert

Thanks again Roy, I'll take another round at optimisation and hope for better results.

Regards,
Mario

Mario,

<< It is as if every time a row is processed the function call is prepared.
Is this possible? >>

No, all called functions are prepared once during the outer SQL preparation,
and then left alone until the outer SQL is un-prepared.

If you want to send me the database catalog and relevant table files, along
with the SQL that you're using, I can profile it here and tell you what's
going on.

But, at first glance, my guess is the same as Roy's:  the EXECUTE IMMEDIATE
is incurring the cost of re-preparing the SQL in the function for each row,
and that's what is taking up the time.  Inside of any routine
(function/procedure/script), the preparation state of statements is always a
nested state based upon the outer SQL's preparation state.

This is essentially what happens with your existing function:

Prepare outer SQL - function is initialized but the EXECUTE IMMEDIATE is not
prepared, etc.
Execute outer SQL - function is executed for each relevant row, and the
EXECUTE IMMEDIATE is executed for each row, incurring the cost of being
prepared (including compilation/table opens), executed, and then destroyed.

With Roy's version, this is what happens:

Prepare outer SQL - function is initialized and the statement/cursor
variables are initialized internally (but not prepared).
Execute outer SQL - function is executed for each relevant row, and the
first execution causes the statement variable to be prepared/executed.
Subsequent row executions only incur the overhead of an additional execution
(due to the explicit PREPARE in the function), and the function is only
destroyed when the outer SQL is un-prepared.

Basically, the rule about manually preparing SQL when you intend to execute
it many times also applies to functions/procedures and the statements
enclosed within them.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Thank you for participating and explanation of the prepare/unprepare cycle.

I've rewrote my function using Roy's advice, and create a copy to do some benchmarking, and unfortunately the response time is about the same.

When an statement is declare inside a function/procedure does it get destroyed after the function is finished and the recreated in the next call?

I would post a copy of the database for both of you to take a look.

Meanwhile, here's a the modified version of the function…


CREATE FUNCTION "fnInvExistenciaBodega2" (IN "pIdProductoEmpresa" GUID COLLATE UNI, IN "pIdBodega" GUID COLLATE UNI, IN "pFechaMov" TIMESTAMP)
RETURNS DECIMAL(19,4)
BEGIN
  DECLARE Result DECIMAL(19,4) DEFAULT 0;
  DECLARE curExs CURSOR FOR stmtExistencia;
  
  PREPARE stmtExistencia FROM
     '
     SELECT px.bodega AS existencia
     FROM  inv_producto_existencia px
        INNER JOIN inv_bodega_ubicacion bu ON px.id_ubicacion = bu.id_ubicacion
     WHERE   px.id_producto_empresa  = ?
     AND     px.fecha  <= ?
     AND     bu.id_bodega = ?
     ORDER BY px.fecha desc
     RANGE 0 TO 1';

  OPEN curExs USING pIdProductoEmpresa, pFechaMov, pIdBodega;

  FETCH FIRST FROM curExs('existencia') INTO Result;

  RETURN COALESCE(Result, 0.0);
END
VERSION 1.00!

Regards,
Mario

Roy and Tim,

You can download a copy of the database from the following URL:

http://www.open-consult.com/merkantus/FunctionTestDB.EDBBkp

The download is about 65mb.

Regards,
Mario

Mario


Is it ansi or unicode?

Is there a download link? If I use the one you've supplied it just opens the backup for display in the browser.


Roy Lambert

Mario,

<< When an statement is declare inside a function/procedure does it get
destroyed after the function is finished and the recreated in the next call?
>>

No, which is what I was attempting to explain.  The outer SQL controls
when the inner referenced functions are created/destroyed.

Your link is bad, BTW.  If you want, you can just email me the backup.  Just
make sure to specify whether it is Unicode or ANSI.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

Sorry for the broken link…

Please try again with this one:

http://www.open-consult.com/merkantus/FunctionTestDB.zip

regards,
Mario

Sorry, I forgot to mention the database its UNICODE.

Regards,
Mario