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:[email protected]]
Sent: Thursday, May 27, 2021 9:29 AM
To: [email protected]
Cc: Virgo Pärna <[email protected]>
Subject: Re: [Firebird-devel] Deprecations
On Thu, 20 May 2021 11:45:22 +0000, Omacht András
<[email protected]<mailto:[email protected]>> 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
[email protected]<mailto:[email protected]>
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
