yes, but if it's was this, why in some condition it's work very fast, and in other condition very slow :(

--- In firebird-support@yahoogroups.com, "Djordje Radovanovic" <softsistem@...> wrote:
>
> 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]
>