Hello,

i found a strange behavior (or a bug?) that i can not explain.

SET TERM !! ;
CREATE OR ALTER PROCEDURE UDF_TEST_1
AS
DECLARE VARIABLE ID VARCHAR(15);
DECLARE VARIABLE S1 VARCHAR(100);
BEGIN

FOR SELECT FIRST 10000 SKIP 0 ID
FROM TABLEA
INTO :ID do
S1 = 'FR-00241095-00241078';

END !!
SET TERM ; !!

execute procedure UDF_TEST_1;
Time to execute the sql: 16 ms
Indexed Read: 74
Non Indexed Read: 10000
Inserts: 0
Updates: 0
Deletes: 0
Time to commit the data: 0 ms

very fast, everything look OK
so the select First 10000 if fast


now i do

SET TERM !! ;
CREATE OR ALTER PROCEDURE UDF_TEST_2
AS
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE S1 VARCHAR(100);
BEGIN

While (I < 10000) DO begin
S1 = AlUdf_COPY('FR-00241095-00241078', 1, 20);
I = I + 1;
END

END !!
SET TERM ; !!

execute procedure UDF_TEST_2;
Time to execute the sql: 15 ms
Indexed Read: 74
Non Indexed Read: 0
Inserts: 0
Updates: 0
Deletes: 0
Time to commit the data: 0 ms

very fast too, so the udf function AlUdf_COPY is fast


OK, and i don't understand anymore, if i do :

SET TERM !! ;
CREATE OR ALTER PROCEDURE UDF_TEST_3
AS
DECLARE VARIABLE ID VARCHAR(15);
DECLARE VARIABLE S1 VARCHAR(100);
BEGIN

FOR SELECT FIRST 10000 SKIP 0 ID
FROM TABLEA
INTO :ID do
S1 = AlUdf_COPY('FR-00241095-00241078', 1, 20);

END !!
SET TERM ; !!

execute procedure UDF_TEST_3;
Time to execute the sql: 827 ms !!!
Indexed Read: 74
Non Indexed Read: 10000
Inserts: 0
Updates: 0
Deletes: 0
Time to commit the data: 0 ms

why now the Execute time is so long ? it's close to 60x more longer than what it's normaly must be ?


thanks by advance for your help !
stephane