ClickHouse vs Oracle vs esProc SPL: Real‑World TPC‑H Benchmark Reveals Surprising Performance Gaps

ClickHouse vs Oracle

We benchmarked ClickHouse (CH) and Oracle (ORA) on identical hardware using the TPC‑H benchmark (8 tables, 22 queries, 100 GB data, single‑node 12 threads). Q1 simple scan shows CH outperforms ORA due to columnar storage, while ORA suffers from row‑store.

For more complex queries (Q2, Q3, Q7) CH’s advantage diminishes: Q2 performance similar to ORA, Q3 CH slightly faster, Q7 CH slower than ORA. Complex queries like Q8 and Q9 cause CH to time out or run out of memory, while ORA completes in a few minutes.

esProc SPL登场

We then evaluated the open‑source esProc SPL, also marketed as high‑performance, using the same TPC‑H tests.

For the complex queries (Q2, Q3, Q7) SPL outperforms both CH and ORA. SPL completes Q8 and Q9 in 37 s and 68 s respectively, far faster than ORA’s 192 s and 234 s, thanks to more efficient algorithms and Java implementation.

In a simple Q1 scan CH is still slightly faster than SPL.

Further gaps

When adding a columnar cursor to SPL, its simple group‑by performance matches CH. Storage size for 800 M rows: CH 5.4 GB, SPL 8 GB, plain text 15 GB, showing CH’s higher compression.

For a join of two group‑by results (SQL3), CH’s runtime doubles while SPL remains unchanged because SPL reuses a single traversal (traversal reuse).

Code example of SPL traversal reuse:

<code>SELECT * FROM (SELECT mod(id,100) AS Aid, max(amount) AS Amax FROM test.t GROUP BY mod(id,100)) A
JOIN (SELECT floor(id/200000) AS Bid, min(amount) AS Bmin FROM test.t GROUP BY floor(id/200000)) B
ON A.Aid = B.Bid</code>

Top‑N tests: CH’s regular Top‑N (SQL2) appears optimized similarly to SPL, but SPL is slightly faster.

Not just speed

For grouped Top‑N (SQL4) CH is 42× slower than SPL because CH performs a full sort, whereas SPL treats Top‑N as an aggregation and scans data once.

SPL code for grouped Top‑N is concise:

<code>=file("topn.ctx").open().cursor@mv(id,amount)
= A1.groups(id%10:gid; top(10;-amount)).news(#2;gid,~.amount)</code>

In funnel analysis (multi‑step event counting), SPL’s script remains short, while equivalent Oracle SQL requires dozens of lines and complex joins.

Overall, CH excels at simple single‑table scans, matching SPL, but SPL consistently outperforms CH in complex calculations, offering higher performance and simpler code.