Oninit® UDR — TimeSeries API: Benchmark

The TimeSeries API against the same data held in a plain relational table: loading, writing, reading and size, and where each one is faster.

The short version

The API has a fixed cost on every call and a low cost per element after that. It is slow on a call that handles one element and fast on a call that handles many.

• Loading: a bulk load is about 3.8× faster than a relational LOAD FROM.
• Writing: one element per call costs about 150 µs, which is 2 to 8× slower than an INSERT. Batches of 50 or more are as fast as an INSERT.
• Reading as JSON: about equal at one hour of data, 1.6 to 1.7× as fast from four hours up, against a relational table that has to build the same JSON.
• Reading as rows: about 5× slower than a plain relational SELECT at every size.
• Size: the data takes about half the space.

What was measured

10 series of 20,160 one-minute points (14 days), three metrics each: 201,600 points. The same values were loaded into a relational table with PRIMARY KEY (series_id, ts) and into TimeSeries columns read and written through oni_ts_read(), oni_ts_write(), oni_ts_write_many(), oni_ts_write_file() and oni_ts_read_rows(). Informix 14.10, TimeSeries 6.01.FC2, one shared instance, timings are client wall clock with the connection set-up subtracted, medians of 5 to 15 runs, measured on 19 October 2026. The relational side varied by more than 10× between runs on a busy instance, so the direction of a result is firmer than its size.

Loading and writing

Speed change is the relational time divided by the API time, less one: plus is faster than the relational table, minus is slower. The bulk load was faster in four of five runs; one run of the API took 10 s against about 2 s in the others.

Writing 2,000 elements in one transaction, by how many go in each call:

The batch rows are level with the INSERT: the relational timing itself ranged from 0.012 to 0.511 s for 2,000 inserts. A one-element call locates the series, opens it and closes it again every time, which is where its 150 µs goes. A batch does that once. Single writes are 8 to 11× slower per element than a batch of 50 or more. Every call stays inside the caller's transaction, so a ROLLBACK removes the elements. The 0.33 s for 2,000 single writes in the first table and the 151 µs per element here (0.30 s) come from different runs.

Reading

One read of a window of one series, milliseconds. The relational side builds the same JSON document that oni_ts_read() returns.

The lead grows to about a day and then stays: seven days is no better than one. A response is limited to 32,739 bytes, so a long window is read in pages of 240 points (42 calls for seven days); those calls are in the figures.

The same read as rows, with oni_ts_read_rows(), against a plain relational SELECT that returns its rows as they are:

A relational read costs about 2 µs per row at every size. The API costs about 0.25 ms per call plus 10 µs per row for rows, and about 0.1 ms plus 9 µs per row for JSON. The gap narrows as the fixed part is spread over more rows and does not close.

What producing JSON costs

Most of the cost of a relational read that must return JSON is the JSON. Microseconds per row, over the four windows above:

Building the JSON is 81 to 85% of the SPL version's time and 92 to 94% of the genbson version's. genbson returns MongoDB extended JSON, with an _id field and ISODate(...) values, which is a different document from the one oni_ts_read() returns. The SPL version is interpreted code; a compiled routine would be faster. For one day of data the relational side costs 17 to 39 ms to produce JSON, against 10 ms for oni_ts_read().

Size

About 33 bytes a point against 70.

Which to use

If a caller has several elements, send them together: oni_ts_write_many() costs about a tenth of a single call per element. To read as rows, state the row type with a cast: oni_ts_read_rows().

Limits of the measurement

• One shape of data: regular one-minute series, three numeric metrics, one index on the relational side. Irregular series, wider rows, concurrent readers and other series lengths were not measured.
• Timings are wall clock on a shared instance, including a start-up cost that is subtracted but not removed. Medians of 5 to 15 runs; the relational side varied by more than 10× between runs.
• The relational JSON is built in interpreted SPL, so those figures bound plain SQL and not the best a relational implementation could do.
• Loads and single writes are measured on oni_ts_combined.4.0; the batch writes on 5.0; the reads on 6.5. The read path is the same in all.