Oninit® UDR — tzdte: Performance

What tzdte costs compared with a native DATETIME, why, and how to keep the cost down.

The short version

tzdte will never be as fast as a native DATETIME. It is a user-defined type. Every comparison, conversion and index-key comparison on it is a call from the engine into our C code, and the engine charges for each call. A DATETIME is built into the engine and never pays that charge. You get what DATETIME cannot give you: the zone, the offset and the rules generation stored with the value. The price is speed, and it cannot be engineered away from our side.

Why it is slower

1. Every operation is a routine call into the engine's UDR interface. We measured a routine that does nothing at all, on plain integers: a scan of 1,000,000 rows that calls it takes 2.6 s, against 0.10 s for a native comparison. That is about 2.6 µs per call (roughly 1.6 µs of user CPU and 0.8 µs of system CPU, including three signal-mask system calls per call). Our own comparison adds about 0.1 µs on top (2.7 s for the same scan). The routine registration options we tried (PARALLELIZABLE, VARIANT, HANDLESNULLS, cost hints) did not change the per-call cost.
2. Indexes and sorts call it for every key comparison. A DATETIME index compares keys natively. A tzdte index makes a routine call for each comparison, and building an index or sorting compares keys millions of times.
3. There is more to do. A tzdte is 16 bytes, not a number of digits. Turning text into a value looks the zone name up and applies its rules. Turning a value into text formats the offset and the zone. A native DATETIME does neither.

What it costs

One session, 1,000,000 rows unless stated, medians of three runs, Informix 14.10 in a container on a shared host with a load average of 8 to 14, so single-session figures are good to about ±10% and insert timings varied by up to 40% between runs. Measured on 5 and 6 October 2026.

Lookups by key hide most of the cost, because an index finds a row with few comparisons. Large scans, index builds and sorts show it most.

What is not slow

• Storage. Writing and reading the 16-byte value costs the same as a DATETIME.
• The time zone rules. Looking up a zone's rules for a conversion costs 0.04 to 0.3 µs, which is a small fraction of the routine call. Default retrieval uses the stored offset and consults no rules.
• Locking. Zone-aware calls take no table locks, and the concurrency tests saw no lock waits or deadlocks.

How to keep the cost down

• Compare against a variable, not a literal. WHERE v > '2026-07-01 00:00 UTC'::tzdte converts the literal on every row (6.3 s). Put the value in a host variable, an SPL variable or a one-row subquery and it is converted once (2.7 s). Do not join to a one-row table of constants: that was slower still.
• Use tzdte_utc(v) for aggregation, grouping and counting distinct values. It returns a native DATETIME, so later steps are native.
• For range queries and ordering on big tables, create a functional index on tzdte_utc(v) and name it with an {+INDEX(table index)} directive. On 300,000 rows it builds in 1.4 s against 14 s for an index on the column. The optimizer does not choose it by itself, and the difference is large: a range query took 0.03 ms (a handful of rows) to 0.7 ms (about 80 rows) with the directive, and about 1.3 s without it, because the optimizer then scans the table.
• Gather statistics, but do not expect them to change tzdte plans. UPDATE STATISTICS HIGH runs on a table with a tzdte column, but it builds no distribution for the tzdte column or for a functional index on it. The optimizer keeps its default selectivity for tzdte predicates (a tenth of the table for a comparison), and none of the plans or timings we measured changed. Statistics still help the other columns of the table. Use the directive above for range queries.
• A BIGINT index key is no faster than tzdte_utc. We measured a BIGINT-valued function as the index key against tzdte_utc(v): no time measurement was more than about 10% better and most were within the noise. Its index is 13% smaller. A BIGINT key built from calendar digits also stops at year 9223, so tzdte_utc remains the recommended key.
• tzdte_utc(v) is exact. The resolution of a tzdte is 10 microseconds, the same as DATETIME YEAR TO FRACTION(5), so the DATETIME it returns loses nothing, and a range on it matches the stored values exactly.
• Fragment large tables and use parallel query. The routines are PARALLELIZABLE. A four-way fragmented table with PDQPRIORITY 100 cut a 1,000,000-row scan from about 2.9 s to 1.1 s (about 2.6×).
• Use tzdte where you need it. If a column never needs a zone, keep it as DATETIME in the hot path and convert at the edges.

How this was measured

Timings are server-side, around one statement, so client transfer is excluded. Tables were raw (unlogged), one per session, 1,000,000 rows for bulk operations; the index, sort and range tests used 300,000 rows. The host was a shared workstation with a load average of 8 to 14 and other containers running, so absolute times will differ on your hardware; the ratios are the useful part, and a difference smaller than about 10% should be read as noise. Figures for four or eight concurrent sessions varied by up to 66% between runs and are not reported here. Statistics were gathered with UPDATE STATISTICS HIGH before the statistics measurements. Not yet measured: scaling on a quiet host, the same tests on Informix 15, and the engine-side call cost on other platforms.