Performance Comparison of ClickHouse, Oracle, and esProc SPL Using TPC‑H Benchmarks

The open‑source analytical database ClickHouse is reputed for speed; we evaluate this claim by comparing ClickHouse (CH) with Oracle (ORA) on identical hardware using the industry‑standard TPC‑H benchmark (22 queries, 100 GB data, 12‑thread single‑node).

For the simple single‑table aggregation Q1, CH outperforms ORA, demonstrating the advantage of its columnar storage for fast table scans.

When query complexity increases (Q2, Q3, Q7), performance diverges: CH matches ORA on Q2, surpasses ORA on Q3, but falls behind on Q7.

For the most demanding queries (Q8, Q9), CH stalls (over 2000 s without result, out‑of‑memory error) while ORA completes in a few minutes.

We then introduce the open‑source esProc SPL, also evaluated with TPC‑H. SPL consistently beats both CH and ORA on the complex queries and is comparable on the simple Q1.

Using SPL’s columnar cursor for a simple GROUP‑BY on 800 M rows yields performance equal to CH, confirming that the cursor eliminates the gap.

SELECT mod(id,100) AS Aid, max(amount) AS Amax FROM test.t GROUP BY mod(id,100)

Storage size comparison shows text data (15 GB), CH (5.4 GB), SPL (8 GB), indicating both CH and SPL employ compression, with CH achieving a higher ratio.

Top‑N queries reveal that both CH and SPL avoid full sorting; SPL is slightly faster.

In a join of two grouped results (SQL3), CH’s execution time doubles, while SPL’s remains unchanged thanks to its "traversal reuse" mechanism.

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

Traversal‑reuse code in SPL (simplified):

=file("topn.ctx").open().cursor@mv(id,amount) cursor A1 =A2.groups(id%100:Aid;max(amount):Amax) =A3.groups(id\200000:Bid;min(amount):Bmin) =A2.join@i(Aid,A3:Bid,Bid,Bmin)

Group‑TopN tests (SQL4) show CH 42× slower than SPL because CH performs a full sort, whereas SPL treats Top‑N as an aggregation requiring only a single pass.

SELECT gid, groupArray(100)(amount) AS amount FROM (SELECT mod(id,10) AS gid, amount FROM test.topn ORDER BY gid ASC, amount DESC) a GROUP BY gid

For a typical e‑commerce funnel analysis, SPL expresses the three‑step funnel in a handful of concise statements, whereas Oracle requires a lengthy, hard‑to‑maintain SQL script.

with e1 as (select gid,1 as step1,min(etime) as t1 from T where etime>=to_date('2021-01-10','yyyy-MM-dd') and etime

In summary, ClickHouse delivers excellent performance for simple scans, matching SPL, but SPL consistently outperforms ClickHouse on complex analytical workloads while offering far simpler, more maintainable code.