On 27/05/2021 07:49, Omacht András wrote: > 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 >
> > The issues that arise: > > - Why is the second case twice as slow as the first? > First case you call UDF. Second case you call PSQL + UDF. > - Why is the fourth case twice as slow as the fifth? > You do the same work as the fifth case plus a PSQL call. > - 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? > In the third case you just do a function call and return a constant, not doing the extra work you do in the others cases. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel