# dataValueSets export stops streaming in 2.43: default ORDER BY plus a lost JDBC fetch size **URL:** \ **Category:** Development - Développement\ **Created:** [7 September 2026 18:46 UTC](https://community.dhis2.org/t/datavaluesets-export-stops-streaming-in-2-43-default-order-by-plus-a-lost-jdbc-fetch-size/76636 "2026-09-07T18:46:36Z")\ **Posts on this page:** 1\ **Page:** 1
**Author:** ![minogin](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/minogin/32/50280_2.png) [@minogin](https://community.dhis2.org/u/minogin)\ **Post date:** [7 September 2026 18:46 UTC](https://community.dhis2.org/t/datavaluesets-export-stops-streaming-in-2-43-default-order-by-plus-a-lost-jdbc-fetch-size/76636/1 "2026-09-07T18:46:36Z")
Second finding from the same benchmarking run as [the JSON serialisation thread](https://community.dhis2.org/t/datavaluesets-json-export-is-7x-slower-in-2-43-than-2-42-cause-identified-fix-exists-upstream-but-is-not-backported/76144). This one is independent of it: it affects **both** JSON and CSV, and it is present unchanged on master. On 2.42 the export wrote rows as PostgreSQL produced them. On 2.43 the entire query is drained before the first byte reaches the client. | Time to first byte, 24-month window | 2.42.5.2 | 2.43.1 | master | | --- | --- | --- | --- | | JSON | 0.16 s | 3.30 s | 3.04 s | | CSV | 0.20 s | 3.23 s | 3.41 s | CSV’s per-value cost didn’t change while its TTFB did, which is what separates this from the serialisation regression. Setup as before: Sierra Leone demo database, PostgreSQL 16, same host and container limits for every run, 973,730 values. ### Two independent causes; either alone produces the delay **An unconditional default `ORDER BY`.** In `HibernateDataExportStore.createExportQuery`: java ```auto if (isEmpty(orders)) orders = List.of(Order.PE, Order.CREATED, Order.DE); ``` `orders` is left null for a plain export request, so the default always applies, producing `ORDER BY pe.startdate, pe.enddate, dv.created, deid`. 2.42’s main export SQL had no `ORDER BY` at all — the only ordering in `SpringDataValueSetStore` was in the paged `lastUpdated` sync variant, which needs it for `limit/offset`. `auto_explain` on 2.43.1, 24-month window: ```auto Sort (cost=18925.34..18925.93 rows=236 width=98) (actual time=2600.226..3028.138 rows=973730 loops=1) Sort Key: pe.startdate, pe.enddate, dv.created, de.uid Sort Method: external merge Disk: 118656kB ``` - Startup time is 2,600 ms out of 3,028 ms — 86% of the query elapses before its first row is available. The sort’s input has a startup time of 1.35 ms and has delivered every row by 1,272 ms. - 116 MB spilled to disk on every export. - Row estimate 236 against 973,730 actual, a 4,126× underestimate. Probably worth a separate look — the CTE plus `= ANY($1)` structure of the generated SQL seems to defeat cardinality estimation here. The top Sort node is byte-identical across JSON, JSON+gzip and CSV. `exportInGroups` and sync mode genuinely need ordered input and request it explicitly. What’s new in 2.43 is that the default is applied to every export whether or not anything downstream consumes it. **No JDBC fetch size on the new path.** 2.42 exported through `jdbcTemplate.query(sql, ResultSetExtractor)`, using the `@Primary` `JdbcTemplate` bean configured with `setFetchSize(1000)`; the service method is `@Transactional`, so autocommit is off — exactly the condition PgJDBC needs for a server-side cursor. 2.43 moved to a Hibernate native query. The Hibernate layer itself is lazy (`NativeQuery.stream()` runs over `scroll(FORWARD_ONLY)`), but no fetch size is applied anywhere on this path: not in `hibernate-default.properties`, not in `HibernateConfig`, no `ConfigurationKey`, and `HibernateNativeQueryAPI` has no way to express it. With PgJDBC’s default of 0 the driver materialises the whole result set in client memory before returning the first row. `DataSourceConfig` still sets 1000 on the `JdbcTemplate` bean — the setting wasn’t removed, the export simply stopped going through the object that carries it. To be straight about the evidence: this second cause is read from source, not observed. The sort blocks first, so it can’t be isolated by latency, and its cost is driver-side memory, which I didn’t measure. ### Why this matters more than three seconds Wall-clock cost of the sort is about 1.76 s on 973,730 rows. The concerns are elsewhere: - **Heap.** With no fetch size, the driver buffers the entire result set per request. At ~974k values that’s survivable; on an instance exporting 5–10M it’s a GC and OOM risk per concurrent export. - **Temp disk under concurrency.** 116 MB of temp writes per export; ten concurrent exports contend for over a gigabyte. - **Client timeouts.** A client with a socket read timeout receives nothing at all until the query finishes, which matters where that takes minutes rather than seconds. ### Suggested fixes 1. Apply a JDBC fetch size on the export query — smallest form is `setFetchSize(n)` in `HibernateNativeQueryAPI.HibernateQuery.stream`, or exposing it on `SQL.Query` so callers can choose. 2. Apply the default `ORDER BY` only where something consumes it; `exportInGroups` and sync mode already request ordering explicitly. Both are needed: with the sort in place, fixing the fetch size alone won’t move the first byte earlier. `HibernateDataExportStore.java` is byte-identical in 2.43.1 and master, so this is present in current development, not only in the release.