Hi Virgo and all!


I also did some tests on 3.0.8.33468



I had a table with 2.8 million records. I tested with its one of the date (not 
timestamp) fields.



Case 1.: UDF call

DECLARE EXTERNAL FUNCTION TO_CHAR

    TIMESTAMP,

    CSTRING(50)

RETURNS CSTRING(50) FREE_IT

ENTRY_POINT 'to_char' MODULE_NAME 'UDF3s';



select count(to_char(date_field, 'YYYY.MM.DD.')) from table



Case 2.: Function UDF call inside

create function case_2 (datum date)

returns varchar(50)

as

begin

  return to_char(datum, 'YYYY.MM.DD.');

end



select count(case_2(date_field)) from table



Case 3.: Function fix string return

create function case_3(datum date)

returns varchar(50)

as

begin

  return '2021.05.27.';

end



select count(case_3(date_field)) from table



Case 4.: Function extract inside

create function case_4 (datum date)

returns varchar(50)

as

begin

  return extract(year from datum) || '.' ||

         lpad(extract(month from datum), 2, '0') || '.' ||

         lpad(extract(day from datum), 2, '0') || '.';

end



select count(case_4(date_field)) from table


Case 5.: Extract in select

select count(extract(year from date_field) || '.' ||

         lpad(extract(month from date_field), 2, '0') || '.' ||

         lpad(extract(day from date_field), 2, '0') || '.') from table





I ran each case ten times. Here are the results:

UDF call

Function UDF call inside

Function fix string return

Function extract inside

Extract in select

AVG:

10,97

20,94

11

21,87

11,91

MIN:

10,2

19,5

9,7

20,7

11,1

MAX:

12,1

22,7

12,2

23,1

13,3




The issues that arise:

-          Why is the second case twice as slow as the first?

-          Why is the fourth case twice as slow as the fifth?

-          If the function call is slow, then why is the third case not as slow 
as the second and fourth? Why is it as fast as the first and fifth?



András





-----Original Message-----
From: Virgo Pärna via Firebird-devel 
[mailto:firebird-devel@lists.sourceforge.net]
Sent: Thursday, May 27, 2021 9:29 AM
To: firebird-devel@lists.sourceforge.net
Cc: Virgo Pärna <virgo.pa...@mail.ee>
Subject: Re: [Firebird-devel] Deprecations



On Thu, 20 May 2021 11:45:22 +0000, Omacht András 
<omacht.and...@libra.hu<mailto:omacht.and...@libra.hu>> wrote:

> UDF has been a compromise for us from the beginning (since IB 6.0 or maybe 
> 5.5). If it is possible, we don’t want to depend on things outside of the 
> database because it is only a problem to have to ask the customers for any 
> extras for the programs to work.

>



                At same time UDF is still faster than UDR/function. I did some 
testing with Firebird 3.0.7 ja 4.0.0RC1. I have UDF with three functions, one 
of which can be rewritten as PSQL function. Results, when used in where 
condition were:

UDF: ~10 sec

UDR/PSQL Function: ~15 sec (UDR and PSQL function had no practical speed 
difference).

PSQL SP: ~19 sec



                For the other two functions it would be nice, if Firebrid 4.0 
could do timezone conversion for TIMESTAMP WITHOUT TIMEZONE type.

Existing database has some fields, that contain timestamps in UTC time, but 
some querys require converting it to local timezone (and some reverse).  And 
since developement enviroment does not have data type for time wiht timezone, 
converting to TIMESTAMP WITH TIMEZONE is not possible.



                Actually, one conversion could be done with:

CAST((CAST(FIELD_WITH_UTC_TIME||'UTC'  AS TIMESTAMP WITH TIME ZONE) AT LOCAL) AS

TIMESTAMP)

But it is ~4.5 times slower than UDF.



--

Virgo Pärna

virgo.pa...@mail.ee<mailto:virgo.pa...@mail.ee>







Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Reply via email to