Inner Join Benchmark¶
| DB | Q2 |
|---|---|
| RayforceDB | 1610 |
| ? (4.0) | 3098 |
| ThePlatform | 34104 |
| DuckDB (multithread turned on) | OOM |
| DuckDB (multithread turned off) | OOM |
| ClickHouse | OOM |
OOM = Out of Memory. Lower values (ms) are better.
Data Generation:
RayforceDB demonstrates exceptional performance in inner join operations:
- While DuckDB and ClickHouse encounter Out of Memory (OOM) errors, RayforceDB successfully completes the inner join query
- RayforceDB (1610ms) is 1.9x faster than ? (4.0) (3098ms)
- RayforceDB is 21.2x faster than ThePlatform (34104ms)
Rayforce Queries¶
(set x (csv [I64 I64 I64 Symbol Symbol Symbol F64] "./db-benchmark/J1_1e7_NA_0_0.csv"))
(set y (csv [I64 I64 I64 Symbol Symbol Symbol F64] "./db-benchmark/J1_1e7_1e7_0_0.csv"))
(timeit (ij [id1 id2] x y)) ;; Q2
DuckDB Queries¶
.timer "on"
create table x as SELECT * FROM read_csv('./db-benchmark/J1_1e7_NA_0_0.csv');
create table y as SELECT * FROM read_csv('./db-benchmark/J1_1e7_1e7_0_0.csv');
select * from x inner join y on x.id1 = y.id1 and x.id2 = y.id2; # Q2
ClickHouse Queries¶
CREATE TABLE x (id1 Int64, id2 Int64, id3 Int64, id4 String, id5 String, id6 String, v1 Float64) ENGINE = Memory;
CREATE TABLE y (id1 Int64, id2 Int64, id3 Int64, id4 String, id5 String, id6 String, v2 Float64) ENGINE = Memory;
clickhouse-client -q "INSERT INTO default.x FORMAT CSV" < ./db-benchmark/J1_1e7_NA_0_0.csv
clickhouse-client -q "INSERT INTO default.y FORMAT CSV" < ./db-benchmark/J1_1e7_1e7_0_0.csv
? (4.0) Queries¶
x: ("JJJSSSF";enlist",") 0: `$":./db-benchmark/J1_1e7_NA_0_0.csv"
y: ("JJJSSSF";enlist",") 0: `$":./db-benchmark/J1_1e7_1e7_0_0.csv"
x ij 2!y // Q2