From UDF to UDR in Firebird 5

Soundex, Cologne Phonetics, PSQL, Free Pascal and Visual C++ in a practical benchmark

IBExpert Ltd - Technical White Paper

Executive Summary

Firebird applications have been able to extend the database engine with external functions for many years. Older installations commonly used UDFs (User Defined Functions). Modern Firebird versions provide UDRs (User Defined Routines), a substantially better integrated architecture.

This white paper follows the transition from UDF to UDR through a practical example: phonetic name matching using Soundex, a German-adapted Soundex variant, Cologne Phonetics, and distance and similarity functions. The same functionality was implemented as Firebird PSQL stored functions, a native Lazarus/Free Pascal UDR, and a native Microsoft Visual C++ 2022 UDR.

The most surprising finding was that programming language was not the dominant performance factor. Once Free Pascal and C++ used a comparable low-level strategy, optimized FPC was typically only about 10 to 25 percent behind Visual C++.

1. From UDF to UDR

UDFs were a proven way to move calculations into external DLLs or shared libraries across many Firebird generations. The old UDF interface, however, belongs to an earlier generation of API design.

UDRs are the modern successor. SQL still calls native code, but integration uses Firebird's modern plugin and object-oriented API, with cleaner handling of data types, NULL values, metadata, character sets and routine lifecycle. For new Firebird 5 extensions, UDR should therefore be regarded as the natural replacement for classic UDFs.

2. Alternative: PSQL stored functions

Many algorithms can be implemented entirely as Firebird PSQL stored functions. This greatly simplifies deployment: no extra DLL, no Linux shared library and no platform-specific binary.

PSQL is particularly attractive when easy installation, backup/restore and platform independence matter more than maximum computational throughput. The real question is not whether PSQL can solve the problem, but whether it is fast enough for the expected call volume.

3. Why phonetic name matching?

Exact string comparisons are often insufficient for names. Klemt, Klemmt, Klempt and Klemp are technically four different strings, but may all be relevant when searching for the same person.

Phonetic algorithms map names to codes that reflect pronunciation more than exact spelling, making typing errors, historical spellings and variants easier to detect.

4. Soundex

Soundex normally creates a short code consisting of an initial letter and digits representing similar consonant groups. It is simple and fast, but was primarily designed for English names.

Our project therefore implemented SOUNDEX and SOUNDEX_DE. The German variant additionally normalizes forms such as Ä/AE, Ö/OE, Ü/UE, ß/SS and common letter combinations.

5. Cologne Phonetics

Cologne Phonetics is often more appropriate for German names. It uses context-sensitive rules and produces a variable-length digit sequence.

Klemt → 4562, Klemmt → 4562, Klempt → 45612, Klemp → 4561. Klemt and Klemmt become phonetically identical, while the other variants remain very close.

6. Distance and similarity

COLOGNE_DISTANCE first calculates Cologne Phonetics for both names and then the Levenshtein distance between the codes. A distance of 0 means identical; 1 means one insertion, deletion or replacement is required.

PHONETIC_SIMILARITY converts this into an easier-to-use value from 0 to 100. In our example Klemt/Klemmt returns 100, Klemt/Klempt 80 and Klemt/Klemp 75.

7. Three implementations, identical results

The five functions SOUNDEX, SOUNDEX_DE, COLOGNE_PHONETIC, COLOGNE_DISTANCE and PHONETIC_SIMILARITY were implemented in PSQL, as a Lazarus/FPC UDR, and as a Visual C++ UDR.

Before performance testing, 10,000 test rows were checked. The final versions produced zero mismatches for all five functions. Identical checksums also confirmed that the implementations processed the same results.

8. PSQL versus native UDR

PSQL proved surprisingly practical for the simpler phonetic functions. As computational work increases, the native UDR advantage becomes much larger, especially for distance and similarity calculations.

The absolute values come from different benchmark forms and should not be interpreted as a pure microbenchmark ratio. The important point is that PSQL is functional and quite capable for simpler tasks, while native UDRs scale much better as procedural computation increases.

9. The first C++ versus Pascal comparison

The first FPC version used convenient UnicodeString processing, UnicodeUpperCase and general string operations. The C++ implementation worked largely on UTF-8 bytes directly. C++ therefore initially appeared four to five times faster in some tests.

This was not a fair compiler-only comparison: the implementations were doing different amounts of internal work.

10. Making the comparison fair

The FPC version was rewritten to use the same low-level strategy: UTF-8 remains byte-oriented in the hot path, German special characters are normalized using their UTF-8 sequences, ASCII uppercasing is performed directly, temporary string operations are reduced, and Cologne/Levenshtein use compact buffers.

The SQL interface and results remained unchanged.

11. Optimization effect in Free Pascal

Most of the performance gain was therefore achieved without changing programming language.

12. Optimized FPC versus Visual C++

With comparable implementations, the gap fell from an apparent factor of four or five to typically about 10 to 25 percent.

13. Why Lazarus needs essentially only Firebird.pas

The Object Pascal binding is compact from the developer's perspective. Firebird.pas bundles the important interfaces and type declarations into a Pascal unit, keeping a small Lazarus UDR project easy to understand and familiar to Delphi/Lazarus developers.

14. Why Visual Studio needs more headers

The C++ UDR uses Firebird's C++ helper infrastructure, including UdrCppEngine.h, Message.h, Interface.h, ibase.h and additional include files and preprocessor helpers.

These are mainly compile-time dependencies. The finished DLL does not simply require all those headers at runtime. Pascal packages many declarations into one unit, while C++ distributes the API across headers, templates and macros.

15. Windows and Linux

PSQL is platform-neutral inside the database. Native UDRs must be built for the target platform, typically a DLL on Windows and a shared library on Linux. The SQL contract can remain the same, but the binary must match the operating system, architecture and Firebird server.

16. Practical decision

For moderate call volumes and maximum simplicity, PSQL is attractive. For high call volumes or computationally intensive algorithms, a native UDR provides substantial headroom.

Where Delphi/Lazarus/FPC expertise already exists, our measurements provide no reason to switch to C++ solely out of performance concerns. C++ remains an excellent choice where the relevant expertise and build infrastructure already exist.

17. The most important lesson

The project started with a question: how should classic Firebird UDF functionality be modernized for Firebird 5? This led to a comparison of UDR and PSQL, and eventually to a direct test of Free Pascal and Visual C++.

The most important result is not one millisecond figure. Architecture, algorithm and data representation often dominate language choice. Our first Pascal implementation was correct but used convenient general Unicode abstractions. The C++ version worked closer to the bytes actually required and was initially dramatically faster. Once the same principles were applied to Free Pascal, most of the difference disappeared.

For developers with many years of Delphi or Free Pascal experience, this is a notable result: well-written Pascal remains highly competitive native code. Visual C++ was still somewhat faster in our test, but the remaining gap was closer to roughly 10 to 25 percent than to a factor of four or five.

Firebird PSQL was also a positive surprise. Not every function justifies a native library. Simple phonetic functions can offer an attractive balance of performance, maintainability and effortless deployment as stored functions. Native UDRs become particularly valuable when high call volumes and more complex calculations occur together.

The practical conclusion is therefore: choose the right algorithm and data representation first, then choose the deployment strategy, and only after that treat the programming language itself as a performance factor.

18. Demo projects and reproducibility

The complete source code is intentionally not reproduced in this white paper, keeping the document readable for managers and users as well as developers.

Demo projects for Firebird PSQL, Lazarus/Free Pascal and Visual Studio/C++ can be provided on request. Benchmark values are snapshots of a specific environment: hardware, Firebird version, compiler options, data distribution, cache state and server load can all affect absolute timings. Reproducible test data, identical results and repeated runs matter more than a single best time.