Improving Data Warehouse Performance: From Clusters and Pre‑Computation to esProc SPL

As data volume and business complexity increase, data‑warehouse performance problems become more severe, leading to long query times, incomplete results, and production incidents.

Traditional remedies include:

Clusters : scaling hardware and distributing tasks, which improves throughput but incurs high cost and cannot handle tasks that cannot be split.

Pre‑computation : materialising results to trade space for time, effective for some analytical scenarios but suffers from poor flexibility and massive storage requirements.

Optimization engines : column stores, vectorised execution, compression, etc., which boost performance on the same hardware but still cannot overcome algorithmic complexity, especially for complex SQL queries.

These approaches often fail for advanced use‑cases such as ordered funnel analysis, multi‑step large‑scale batch jobs, and high‑dimensional metric calculations, where SQL becomes unwieldy or impossible.

To break the SQL limitation, a non‑SQL computation model is needed, allowing programmers to control execution logic, apply low‑complexity algorithms, and fully exploit engineering optimisations.

esProc SPL provides such a model. It introduces a new Structured Process Language (SPL) with richer data types, extensive libraries, and a procedural style that lets developers write concise, natural‑thinking code for complex, multi‑step analytics.

Performance is achieved through:

Low‑complexity algorithms that reduce computational steps.

Engineered storage (binary files with columnar, ordered, compressed, parallel‑segmented formats) that matches algorithmic needs.

Additional engineering tricks such as column storage, compression, large‑memory usage, and vectorised execution, often delivering several‑fold speedups.

Example: a three‑step e‑commerce funnel analysis that takes 3 minutes on a Snowflake medium cluster (4 × 8 = 32 cores) in SQL versus under 10 seconds on a 12‑core, 1.7 GB server using SPL.

Code comparison (SQL vs SPL): SELECT TOP 10 x FROM T ORDER BY x DESC When rewritten as a grouped Top‑N in SPL, the operation becomes a simple aggregation without full sorting, achieving the desired low‑complexity performance.

Full SQL implementation (simplified excerpt):

WITH e1 AS (SELECT uid, 1 AS step1, MIN(etime) AS t1 FROM event WHERE etime>=TO_DATE('2021-01-10') AND etime<TO_DATE('2021-01-25') AND eventtype='eventtype1' ... GROUP BY 1),
 e2 AS (SELECT uid, 1 AS step2, MIN(e1.t1) AS t1, MIN(e2.etime) AS t2 FROM event e2 INNER JOIN e1 ON e2.uid=e1.uid WHERE e2.etime>=TO_DATE('2021-01-10') AND e2.etime<TO_DATE('2021-01-25') AND e2.etime>t1 AND e2.etime<t1+7 AND eventtype='eventtype2' ... GROUP BY 1),
 ...
SELECT SUM(step1) AS step1, SUM(step2) AS step2, SUM(step3) AS step3 FROM e1 LEFT JOIN e2 ON e1.uid=e2.uid LEFT JOIN e3 ON e2.uid=e3.uid;

SPL implementation (simplified excerpt):

=["etype1","etype2","etype3"]
=file("event.ctx").open()
=A2.cursor(id,etime,etype;etime>=date("2021-01-10") && etime<date("2021-01-25") && A1.contain(etype) && ...)
=A3.group(uid).(~.sort(etime))
=A4.new(~.select@1(etype==A1(1)):first,~:all).select(first)
=A5.(A1.(t=if(#==1,t1=first.etime,if(t,all.select@1(etype==A1.~ && etime>t && etime<t1+7).etime, null))))
=A6.groups(;count(~(1)):STEP1,count(~(2)):STEP2,count(~(3)):STEP3)

The SPL version is markedly shorter and can handle an arbitrary number of funnel steps by adjusting parameters, whereas each additional step in SQL requires a new sub‑query.

Overall, esProc SPL demonstrates that by first maximising single‑node performance through low‑complexity algorithms and engineering tricks, and only scaling out with clusters when necessary, one can achieve “single‑node‑level‑of‑cluster” performance for many big‑data workloads.

While SPL lacks the extensive automatic optimisation of mature SQL engines and demands developers learn its paradigm, the potential performance gains and cost reductions often outweigh the learning curve.