4.5× Faster SAP Table Reads — and the WHERE Clause That Wasn't There
sap_read_table reads a table out of SAP over RFC and hands it to DuckDB. It has
been in ERPL since the beginning, and for most of that time it has been doing two
things badly that I did not notice, because both of them look exactly like "SAP is
slow" from the outside.
The first: it parallelised across columns. One concurrent RFC_READ_TABLE
call per projected column, all reading the same rows in lock-step. Read fifty
columns and you get fifty calls in flight. Read one column out of a large table —
which is what an incremental extract usually looks like — and you get one call at a
time, with a threads parameter that has nothing to spread.
The second is worse, and I will get to it.
Where the time actually goes
Before changing anything I put a release build under perf reading 2.9 million
rows out of REPOSRC. The profile is the whole argument for what follows:
| Cost | Share |
|---|---|
RfcRecord::getValue — SDK field read | 16.3% |
CsRDecomprLZH — SAP wire decompression | 14.1% |
Utf16nToUtf8nBase — SDK encoding conversion | 13.5% |
isspace — libc, from trailing-blank trimming | 8.2% |
uc2std — our UTF-16 to std::string | 7.4% |
About half the time is decompressing and re-encoding bytes that SAP sent us. That matters for two reasons. It is CPU work, not waiting — so spreading it across cores actually helps. And every one of those bytes belongs to a row we asked for, so the cheapest possible optimisation is to stop asking for rows we are going to throw away.
Which brings me to the second thing sap_read_table was doing badly.
The WHERE clause that wasn't there
RFC_READ_TABLE takes an OPTIONS table holding an ABAP WHERE clause. ERPL has
always translated some SQL predicates into it. Reading the translation code, "some"
turned out to be narrower than I remembered: equality, and IN lists of at most five
values. Everything else — <, >, BETWEEN, AND, OR, a sixth value in an IN
list — was dropped and left for DuckDB to evaluate after the fact.
That is merely wasteful. This is the part that is not:
SELECT count(*) FROM sap_read_table('/DMO/FLIGHT') WHERE SEATS_MAX > 350;
-- 40
Forty is the whole table. The answer is fourteen.
DuckDB's table-function contract says it plainly: setting filter_pushdown = true
means "if not supported a filter will be added" — so when you do support it,
DuckDB removes the filter from the plan and the scan becomes solely responsible for
it. EXPLAIN confirms there is no FILTER operator above SAP_READ_TABLE. ERPL
was translating what it could, silently discarding the rest, and returning the rows
anyway.
Every range, every conjunction, every wide IN was affected. If you have ever
filtered a SAP table from SQL with anything other than =, you were getting more
rows than you asked for, and DuckDB was not double-checking.
Both halves are now fixed. Predicates that can go to SAP do:
| Predicate | Pushed |
|---|---|
=, <>, <, >, <=, >= | yes |
AND / OR, including BETWEEN | yes, all arms or none |
IN (...) | yes, up to a clause-length budget |
IS NULL | no — ABAP has no NULL |
anything on the client field (MANDT) | no — RFC_READ_TABLE rejects the clause |
And predicates that cannot are evaluated by ERPL itself, so the result is the same either way. Only the volume on the wire changes.
Two SAP details cost me an afternoon each and are worth writing down. Literals must
match the DDIC type: push a DATE as 2020-01-01 and the server answers
"is not a valid value for D(8,0)" — DATS wants YYYYMMDD. And the client field
can never appear in OPTIONS at all; a join on MANDT produces exactly that clause,
which is why the old code had a comment about not pushing conjunctions "for joins".
Partitions: giving a narrow read something to spread
Filter pushdown moves fewer rows. Partitioning moves the remaining ones in parallel.
RFC_READ_TABLE takes ROWSKIPS and ROWCOUNT, so a table can be read in windows.
partitions hands each worker its own window:
SELECT * FROM sap_read_table('REPOSRC', partitions = 8);
-- or as a session default
SET erpl_rfc_partitions = 8;
Measured on 2.87 million rows of REPOSRC, single column, release build:
partitions | 1 | 2 | 4 | 8 | 12 |
|---|---|---|---|---|---|
| wall | 94.1s | 50.6s | 30.0s | 21.0s | 20.5s |
4.5× at eight workers, and the knee is visible — twelve buys almost nothing over eight. Where that knee falls is a property of your SAP system's capacity, not of ERPL, so find it by raising the number and watching throughput rather than trusting mine.
Row counts and order-independent checksums are identical at every partition count. I say checksums rather than counts deliberately: a count alone cannot see a scan that drops one window and duplicates another.
What it costs
Parallel readers are not free, and the interesting question is what you pay:
partitions | peak RSS | wall |
|---|---|---|
| 1 | 215 MB | 104.1s |
| 8 | 374 MB | 24.9s |
1.7× the memory for 4.2× the speed. That ratio is not automatic — each worker owns
its own SAP result buffers, so the naive version of this scales memory linearly with
the worker count. erpl_rfc_fetch_size is now divided across workers, which is why
eight of them cost 1.7× rather than 8×.
On a BSEG-shaped table
The single-column case is where partitioning shines, because that is where the old column-parallel path had nothing to work with. Wide tables already had some parallelism, so the gain is smaller and the knee arrives sooner.
ZWIDE_BSEG is the 400-column BSEG-shaped fixture from
erpl-rev. Reading 100,000 rows of a
50-column slice:
partitions | 1 | 2 | 4 | 8 |
|---|---|---|---|---|
| wall | 1.81s | 1.25s | 0.85s | 1.74s |
That is about 118,000 rows/s at four workers, and a regression at eight. The regression is not mysterious. Each worker gets its own share of the memory budget, so adding workers shrinks the batch each one reads, and a smaller batch means more round-trips:
partitions | batch size | RFC calls for this read |
|---|---|---|
| 1 | 16384 | 350 |
| 4 | 4096 | 1250 |
| 8 | 2048 | 2450 |
Going from four workers to eight doubles the parallelism and very nearly doubles the
call count, so the two cancel. If you want to partition a wide table harder, raise
erpl_rfc_fetch_size along with it and pay for the throughput in memory.
For scale, the erpl-rev post measured ~167,000 rows/s on the same fixture shape and
the same container. These are not the same measurement and I would rather say so
than imply a tie: erpl-rev is a registered RFC server that ABAP pushes into, running
against 10 million rows; this is a pull over RFC_READ_TABLE against 100,000. A
hundred-fold extrapolation from my number would not be honest. What the comparison
does say is that an ordinary SQL SELECT is now in the same order of magnitude as
the purpose-built prototype, which was not true before.
When not to use it
Partitioned workers finish in whatever order they finish. An unpartitioned scan calls
RFC_READ_TABLE with GET_SORTED='X' and returns rows sorted; a partitioned one does
not. That is why it is opt-in rather than the default, and why you should add an
ORDER BY if you were relying on the old behaviour.
ROWSKIPS is also an ABAP INT4, so a window cannot start past row 2,147,483,647.
ERPL refuses rather than wrapping — a wrap would silently re-read an earlier range and
duplicate rows, which is the worst thing this code could do.
And more workers is not monotonically better. Reading ten million rows of D010TAB —
a 28-million-row dictionary table — four workers finished in 405 seconds and eight
workers came back in seven with a database error:
ID:SAIS Type:E Number:000 DB_Error on D010TAB:
The database registered an internal error.
That is SAP declining, not ERPL failing, and it is worth knowing that it can happen.
The same eight workers are perfectly happy against REPOSRC, so the limit is a
property of the table and the system rather than a number I can give you. It fails
loudly and immediately, which is the behaviour you want: no partial result, no silent
truncation, just a refusal naming the table.
ODP: the knob that was compiled in
sap_odp_read_full fetches ODP data in packages, and I_MAXPACKAGESIZE controls how
much SAP puts in each one. It was set to 2 MiB in the source and reachable from
nowhere — erpl_odp registered no settings at all.
That mattered more than it sounds. Counting packages on the test source:
fetch_size | packages |
|---|---|
| 2 MiB (the old fixed value) | 3 |
| 256 KiB | 14 |
| 16 KiB | 195 |
Three packages. The suite had a test sweeping THREADS from 1 to 8 to prove
parallelism was safe — against a source that produced three units of work. Most
workers found the queue already drained. The test was not wrong, it was just not
testing what its name claimed.
fetch_size and threads are now settings on ODP with the same names and meanings
they have on sap_read_table, so what you learn on one applies to the other. And
sap_odp_read_full no longer crashes when DuckDB opens more workers than there are
packages — it dereferenced a null local state, which you hit by asking for
threads := 8 on a small source.
BICS: memory tracks rows, not cells
erpl_bics reads BEx query results, and BW wants a budget up front:
I_MAX_DATA_CELLS, a cell count. ERPL derived it by dividing your memory budget by an
assumed cost per cell — 128 bytes, a number I had taken from the width of one row in
the SDK's data-cell structure.
Measuring it on a release build produced a result I did not expect:
| result rows | column axis | peak RSS |
|---|---|---|
| 195 | — | 612 MB |
| 2,523 | — | 623 MB |
| 46,755 | — | 666 MB |
| 2,523 | one characteristic | 637 MB |
| 2,523 | two characteristics | 614 MB |
The last two lines are the point. Adding characteristics to the column axis multiplies the data-cell count many times over and does not move peak memory at all. Memory tracks the number of result rows, at roughly 1.16 KB each, plus about 605 MB that a fetch costs before its first row.
So a cell budget is a poor proxy for memory, and 128 bytes per cell was measuring the
wrong thing. That does not make I_MAX_DATA_CELLS useless — every row carries at
least one cell, so capping cells does cap rows — but it does mean the number ERPL used
to quote you was misleading. It now tells you what the query will really cost:
... would return 46755 rows x 6 columns = 280530 data cells, which needs about
34.2 MB of memory. ... Note that BICS memory scales with rows rather than cells:
reading 46755 rows is expected to need about 657.1 MB whatever the column count.
657 MB predicted against 666 MB measured. The old message said 34 MB and suggested a setting that would have let the query through and then run you out of memory.
sap_bics_begin also gained the drilldown parameters it had been advertising:
SELECT * FROM sap_bics_begin('0D_NW_C01', id := 'q1',
rows := ['0D_NW_DIV'],
columns := ['0D_NW_CNTRY']);
Those were declared and then never read — accepted with no error and no effect. They work now, and building a query this way costs three fewer full session round-trips than the statement-by-statement form.
What I would tell you to do with this
If you filter SAP tables from SQL, upgrade — the unapplied-predicate bug is in released ERPL and it returns wrong rows rather than failing.
If you extract few columns from a large table, set partitions. Start at 4, try 8,
watch throughput, and stop when it stops improving. Check with your Basis team before
pointing many parallel readers at a production system; SAP-side concurrency limits
apply per client program.
If you read wide tables, you already had parallelism and partitions will help less —
a low value may still be worth it.
INSTALL erpl FROM 'http://get.erpl.io';
LOAD erpl;
One last number, for the honest column. Almost everything above was found by measuring rather than reading: the profile, the memory curve, the package count, the fact that a 40-row test fixture had been hiding a scan that truncated on multi-batch tables. The code had been read plenty of times. It was the measuring that was missing.