On 27/05/2021 04:29, Virgo Pärna via Firebird-devel wrote: > On Thu, 20 May 2021 11:45:22 +0000, Omacht András <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 >
UDR will always be slower than UDF, after all UDF is insecure (does not validate all things to be secure). But UDR could be improved. It has a thin layer AS PSQL functions, where it checks input and output parameters constraints and defaults. That layer, if implemented different, could make things faster than now. > 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. > You can SET TIME ZONE 'UTC'; Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel