Possible reason is smallint and integer.
I believe that 32767 - 32744 = 23 and internaly it has overhad of 24 chars so it must use much larger integer for 32744 than smallint 32743. You just found boundaries of smallint and integer.

Regards,

Djordje Radovanovic

----- Original Message -----
From: svanderclock
To: firebird-support@yahoogroups.com
Sent: Monday, March 08, 2010 14:14
Subject: [firebird-support] Re: Strange behavior (bug?) with udf



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
>





[Non-text portions of this message have been removed]