Adriano, can you tell me what are the extra jobs that make calling through a function such a significant slowdown in other cases?
It will be difficult to change from UDF to function if the runtime is doubled in such a simple case as it is in the fourth case compared to the fifth. And, although I haven’t tested UDF yet, there were no good numbers about it before either (slower than PSQL(?)) : https://sourceforge.net/p/firebird/mailman/message/36706890/ András -----Original Message----- From: Adriano dos Santos Fernandes [mailto:adrian...@gmail.com] Sent: Thursday, May 27, 2021 1:33 PM To: firebird-devel@lists.sourceforge.net Subject: Re: [Firebird-devel] Deprecations 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 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
