dict merge in Python is ~1.19× faster than JOIN in SQL

At 30,000 rows, merging in Python is 19% faster than a SQL JOIN — the join table has only 5 rows, so pushing everything into SQL is not automatically right.

Result

Run	dict merge in Python	JOIN in SQL	Ratio
Run 1	32.103	38.133	0.84×
Run 2	34.156	39.789	0.86×
Run 3	31.930	38.298	0.83×

Spread over 3 runs: dict merge in Python CV 3.1% / JOIN in SQL CV 1.9% / Ratio CV 1.2%

The statement

tags = dict(con.execute('SELECT tag,label FROM tags'))
rows = con.execute('SELECT id,tag FROM items LIMIT 30000').fetchall()
sum(1 for _i,t in rows if tags.get(t)=='BETA')

SELECT COUNT(*) FROM items i JOIN tags t ON i.tag=t.tag WHERE t.label='BETA'

Conditions

30,000 of 100,000 `items` rows against a 5-row `tags` table.

Where this number does not apply

With a larger join table, or further SQL filtering, JOIN wins.

Reproduce

cd bench && python sqbench.py --samples 9 --out ../data/run.json