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