I just discover something interesting :

you can also use the build in function substr instead of aludf_copy it's the same result..

if the declaration is

DECLARE EXTERNAL FUNCTION substr
CSTRING(32744) CHARACTER SET ISO8859_1,
SMALLINT,
SMALLINT
RETURNS CSTRING(32744) CHARACTER SET ISO8859_1 FREE_IT
ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';

Time to execute the sql: 827 ms

if the declaration is (we just replace 32744 by the lower value 32743)

DECLARE EXTERNAL FUNCTION substr
CSTRING(32743) CHARACTER SET ISO8859_1,
SMALLINT,
SMALLINT
RETURNS CSTRING(32743) CHARACTER SET ISO8859_1 FREE_IT
ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';

Time to execute the sql: 78 ms !

someone can explain why only 1 bytes more can cause the function to be 100x more slower in some particular condition ?

thanks y advance
stephane

--- In firebird-support@yahoogroups.com, "svanderclock" <svanderclock@...> wrote:
>
> 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
>