On 27/05/2021 08:48, Omacht András wrote: > 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/ >
When you have expressions like 'constant' versus lpad(extract(...)) || lpad(extract(...)) you're comparing different things, the second case certainly being much slower. The same when you have an expression inside a function versus the same expression inlined in SQL. Firebird SQL/PSQL optimizes very few things in relation to statements/expressions and it executes interpreted. In relation to UDR, it's a layer that has a cost to be traversed many times. But UDR implementation (user code) could be done in different ways, and some ways are faster (pre-compute buffer positions in prepare time versus execution time). UDR is flexible. Not everyone needs a routine fast enough to be called million of times and may do simple implementations. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel