Local (rddads) INDEX ON is ~11× slower than SAP ACE on real-world DBFCDX

[rkedzioralmaalpinex]

Issue draft — OpenADS v1.6.0 performance regression on local INDEX ON

Title: Local (rddads) INDEX ON is ~11× slower than SAP ACE on real-world DBFCDX

Symptom

Rebuilding the CDX bag of a single production table takes:

Engine	Time
SAP ACE 11.10 (ace64.dll)	0:28
OpenADS 1.6.0 (this build)	5:16

Same DBF, same expressions, same host, single-threaded local access, no server involved. That is roughly an 11× slowdown.

Environment

• OpenADS: v1.6.0 prebuilt Windows x64 release + a locally rebuilt DLL using openads_ace_mapped.def (SAP ordinal remap per docs/en/ordinal-compat.md) — both showed the same regression.
• Host: Windows 11 Pro x64, MSVC 2026 (v18) toolchain.
• Harbour: 3.2dev, contrib/rddads (name-linked build for the isolated repro; production consumer is ordinal-linked).
• Mode: AdsSetServerType(ADS_LOCAL_SERVER), AdsSetFileType(2) (DBFCDX), AdsSetCharType(ADS_OEM, .T.), AdsCacheOpenTables(1000), AdsCacheOpenCursors(500), AdsLocking(.T.), AdsRightsCheck(.F.).
• CDX collation: NTXPL852 (from adslocal.cfg).

Workload scale (real production dataset)

A single client's database directory:

• 609 DBF files totalling ~5.6 GB (top individual files: WOP_ARC.DBF 318 MB, ZAP.DBF (monthly, several copies) 200-300 MB each, NOTY_R.DBF 270 MB).
• 374 CDX files, each typically holding 10-16 tags (production example ASORTYM.CDX: 16 tags, ~1.9 MB).
• Root-level global dictionaries (~65 DBF) plus per-magazine per-month subdirs (2026/01/…2026/07/…) each containing full document/asortyment/transaction tables.

A full reindex is a routine maintenance action (e.g., after a crash-recovery or during migration). On SAP ACE it completes in minutes; scaled by the 11× factor observed on a single table, on OpenADS 1.6.0 the same job would take hours, which makes it operationally unusable.

Isolated repro (single table)

Table: kopia ASORTYM.DBF (14 118 records, 125 columns, ~21 MB) + 16-tag ASORTYM.CDX.

Repro script (Harbour, contrib/rddads, ace64.dll side-by-side):

REQUEST ADSCDX
PROCEDURE Main()
   LOCAL nStart
   rddSetDefault("ADSCDX")
   AdsSetServerType(1); AdsSetCharType(2, .T.); AdsSetFileType(2)
   AdsCacheOpenTables(1000); AdsCacheOpenCursors(500)
   AdsLocking(.T.); AdsRightsCheck(.F.)

   USE ASORTYM VIA "ADSCDX" NEW EXCLUSIVE
   FErase("ASORTYM.cdx")   // drop existing CDX to force full rebuild
   nStart := Seconds()
   INDEX ON Upper(A_NAZWA)        TAG A_NAZWA
   INDEX ON Val(A_OZNACZE)        TAG A_OZNACZ
   INDEX ON A_KOD1                TAG A_KOD1
   INDEX ON A_KOD2                TAG A_KOD2
   // ... 12 more tags analogous to production CDX
   ? "Elapsed:", Seconds() - nStart
   USE
RETURN

What we already ruled out

• Not I/O: source DBF is on the same local NVMe for both engines, warm cache; SAP finishes in 28 s reading the same bytes.
• Not AdsRightsCheck / locking: identical flags used.
• Not ordinal drift: we rebuilt OpenADS with SAP-ordinal .def and the perf number is unchanged from the stock release DLL.
• Not tag count: even single-tag INDEX ON Val(A_OZNACZE) on the same table is visibly slower than the full 16-tag build on SAP.

Ask

• Is there a known perf bottleneck in the CDX write path (leaf allocation, key-encoding, or collation lookup for NTXPL852) that could account for an ~11× cost?
• Are there compile-time or runtime options we should try (e.g., a bulk-load mode) before this is treated as a bug?
• Would you like a dumpbin-style profile or a self-contained repro archive (anonymised DBF + the .prg above)? Happy to attach if it helps.

Related earlier issue: #127 (numeric dbSeek + NTXPL852 collation) — that was fixed in v1.6.0 based on our earlier isolated tests, so we're re-evaluating adoption; this is the last remaining blocker.

Thanks for the fast pace of releases — the 1.5.x → 1.6.0 progress on correctness has been remarkable, hoping the write path can follow.

[FiveTechSoft]

Thanks for the detailed repro and the 11× measurement against v1.6.0 — that was the current release when this issue was opened (2026-07-02).

What was already in v1.6.0 when you benchmarked

Your test ran after the main CDX bulk-build optimisation landed (commit 2e65e3f, shipped from v1.5.1 onward):

• build_bulk() on AdsCreateIndex61 — each Harbour INDEX ON / AdsCreateIndex for CDX now collects all keys in one table walk, sorts in memory, and emits the B+tree bottom-up (~10× faster tree construction vs per-record insert()).
• Sibling-tag resync guard — when creating tag N in a multi-tag bag, siblings that already have a populated tree are re-parked instead of rebuilt from scratch, avoiding the O(tags²) full-table re-scan that used to make 10–16 tag builds take minutes.

So the 5:16 vs 0:28 gap you saw on v1.6.0 likely sits outside raw B+tree insertion — e.g. per-tag expression evaluation over 14k rows × 125 columns (Upper(), Val(), …), record loading, or other CREATE INDEX overhead we have not profiled head-to-head against SAP yet.

Improvements since v1.6.0 (v1.6.1 → v1.8.0)

Area	Release	Relevance to your repro
Bulk REINDEX — Table::reindex() for CDX now uses clear_data() + build_bulk() (same path as CREATE INDEX) instead of erase-then-insert	v1.8.0	Helps REINDEX / maintenance rebuilds; your script uses sequential INDEX ON, not AdsReindex
NTXPL852 / PL852 OEM collation — AdsSetCollation + CDX key compare/build honour Polish CP-852 (Ł between L and M)	v1.8.0	Correctness for your adslocal.cfg collation; minor compare cost, not the main build bottleneck
Remote OrdKeyCount, AdsGetDate, xBrowse nav, DD API, scope fixes	v1.6.4–v1.7.0	Unrelated to local INDEX ON perf

v1.8.0 release: https://github.com/FiveTechSoft/OpenADS/releases/tag/v1.8.0

Ask — re-test with v1.8.0

We have not re-run your isolated ASORTYM repro (14 118 rows, 16 tags, NTXPL852) against v1.8.0. It would be very helpful if you could repeat the same Harbour script side-by-side:

1. SAP ACE 11.10 (baseline you already have)
2. OpenADS v1.8.0 ace64.dll (same flags: ADS_LOCAL_SERVER, ADS_OEM, NTXPL852)

If the gap is still ~11× on INDEX ON, that confirms the bottleneck is in the per-tag key-evaluation / table-walk layer rather than B+tree write — and we can prioritise profiling that path (and optionally add a dedicated perf benchmark to CI).

If you can share anonymised timing per tag (or a self-contained archive as you offered), we will use it to track progress on this issue.

Related fixes since your report: #127 (numeric seek + NTXPL852) closed in v1.8.0; this perf item remains open until we have a fresh v1.8.0 measurement.