Picking Validate Performance β Vanilla / SQL kernel / GBStyle ORM
Tags: #performance #stock #odoo-core #gbstyle
Measured 2026-09-19 (1,500-line run), 2026-08-26 (1,000-SKU lab) Β· Project GB STYLE ERP Β· Author Grant Kim
1. Summary
On a ~1,500-line picking validate, the SQL kernel path took 21s and 7,812 queries β about 4x faster and roughly a tenth of the queries of the ORM path on the same codebase (81s, 79,325 queries).
- The prod-clone ORM (81s) is slower than vanilla Odoo ORM (49s). Not because of line count, but because of GBStyle write hooks and a larger quant / valuation / rule dataset.
- The SQL kernel beats vanilla Odoo too (21s vs 49s). It does not merely offset the cost of our customisation β it gets past core ORM's line-by-line processing itself.
- Queries grew 2.8x but wall time only 1.7x. Many of the extra statements are cheap SELECTs; wall time is still dominated by quant and valuation writes.
- The three columns are not the same code path. Whenever these numbers are quoted, state which stack they came from.
2. Background β why we measured this
2.1 The problem: validating a long picking is slow
GBStyle handles bulk pickings with 1,000β1,500 lines on a single outgoing document. Validating that document in Odoo applies stock quantities (quants), move states, inventory valuation and downstream reservations all at once. Odoo core does this by walking the lines one at a time through the ORM. The more lines, the steeper the cost β and a warehouse operator waits tens of seconds on a single button.
2.2 What we built: the SQL kernel
To cut that, we added an SQL kernel (validate_full_state_sql) in bss_gbstyle_quant_imp. Instead of calling the ORM per line, it applies quant deltas and move / picking state set-based, in a handful of SQL statements. We kept a way back: a setting or a context flag returns to the original ORM path at any time, in case the kernel misbehaves.
2.3 The two questions
- How much faster is the SQL kernel in practice?
- Is the ORM path slow because of our customisation, or because of Odoo's structure? β answering that needs a baseline. So we stood up a vanilla Odoo instance (
odoo19_vanilla_ee) with no addons at all as the third comparison.
2.4 Why we report queries alongside time
Wall time moves with server specs and the load of the day. Query count, by contrast, shows how many times the code talks to the database β it stays comparable across environments. Reading both together separates "the server is slow" from "the code does too much work".
2.5 Terms
- ORM β Odoo's layer for handling the database through Python objects. Convenient to develop against, but it issues SQL per record, which hurts on bulk work.
- quant β the on-hand stock record per location and product. Updated on every validate.
- putaway β the rule deciding where incoming goods are placed. Not cheap; re-running it repeatedly adds up.
- Vanilla β plain Odoo with no customisation addons installed.
3. What was compared
This is not one document with a setting toggled. The three paths ran on different stacks.
| Path | What actually ran | Environment |
|---|---|---|
| Vanilla ORM | Core Odoo button_validate only | Instance odoo19_vanilla_ee, port 8029. No bss_gbstyle addons |
| Prod-clone SQL | validate_full_state_sql kernel in bss_gbstyle_quant_imp | Prod-clone DB and code. Set-based quant / state |
| Prod-clone ORM | Same clone, SQL kernel switched off | gbstyle_disable_validate_full_state_sql. Full GBStyle ORM stack (main, Cafe24, barcode, putaway, ACL) |
How the ORM retest was forced. Settings were not changed; the ORM path was forced by context. The log confirms it: full-state SQL disabled by context β super_button_validate. Document OUT/50375 was left untouched; a new same-shape chain was created for the ORM run.
4. Results β ~1,500-line validate (2026-09-19)
| Vanilla ORM | Prod-clone SQL | Prod-clone ORM | |
|---|---|---|---|
| Document | KCWH/PICK/00004 | β¦/OUT/50375 | β¦/OUT/50424 |
| Lines | 1,498 | 1,500 | 1,494 |
| Validate time | 49s | 21s | 81s |
| Queries | 28,718 | 7,812 | 79,325 |
| Per line | ~19 | ~5 | ~53 |
| Next step | Assigned | Assigned | Assigned |
Ratios β same shape, different stack:
| Comparison | Time | Queries |
|---|---|---|
| Prod SQL vs prod ORM | ~4x faster | ~10x fewer |
| Vanilla vs prod ORM | ~1.7x faster | ~2.8x fewer |
| Prod SQL vs vanilla | ~2.3x faster | ~3.7x fewer |
5. Why the query counts differ
Prod ORM 79k vs vanilla 29k (~2.8x, ~34 extra queries per line)
- Every
stock.movestatewrite inbss_gbstyle_mainre-runs putaway and may writelocation_final_idagain. - Stored picking computes, Cafe24
button_validatepre/post, branch ACL, chatter, downstream assign. - A fat
stock_quant, real-time valuation andir.rulemake the same ORM methods issue more statements.
Prod ORM 79k vs prod SQL 7.8k (~10x)
- SQL applies quant deltas and move / picking state in a few statements.
- ORM still walks per-line
write/_gather/ reserve.
Vanilla 29k vs prod SQL 7.8k (~3.7x)
- Vanilla is still line-by-line core ORM.
- On a comparable 1,500-line picking, the SQL kernel makes fewer round-trips than a clean Odoo.
Time grew less than queries (49s β 81s) because many of the extra statements are cheap SELECTs. Wall time is still dominated by quant and valuation writes.
6. Reference benchmark β 1,000-SKU chain (2026-08-26)
This lab is GBStyle ORM vs SQL; it does not include the vanilla instance. Flow: WH/Stock β WH/Out β Branch Transit β Branch Stock. Source: sql_path_benchmark_chain_final_opt_1000c.json, local DB gbstyle_dev_remote_20260622.
| Phase | ORM (ms) | Final SQL (ms) | Faster |
|---|---|---|---|
| step1 confirm | 84,791 | 1,472 | 98.3% |
| step1 validate + push | 103,505 | 10,763 | 89.6% |
| step2 assign | 88,849 | 788 | 99.1% |
| step2 validate + push | 72,527 | 9,294 | 87.2% |
| step3 assign | 50,599 | 792 | 98.4% |
| step3 validate | 19,771 | 1,987 | 90.0% |
| Hot path total | 420,714 (~7.0 min) | 25,809 (~26s) | 93.9% |
The gap is widest on the assign phases (98β99% cut); validate + push improves less (87β90%) because real stock work remains there even on the SQL path.
Two caveats to read alongside the table
- The six phases above sum to ~420.0s ORM and ~25.1s SQL, slightly below the stated hot-path totals (420.7s / 25.8s). The totals appear to include steps not listed here.
- The 30β40s walls on 120-SKU runs in older slides are mostly fixture setup. Business confirm + assign + validate on that run was ~6.3s ORM vs ~2.1s SQL.
7. What this means
- The performance problem starts with structure, not with our customisation. Vanilla Odoo still spends 49s and 28.7k queries on 1,500 lines. Line-by-line ORM traversal is the bottleneck; GBStyle hooks add load on top of it.
- The headroom is in the hooks. Re-running putaway on every
statewrite is a candidate to cut even where the ORM path must be used. - Keep the ORM path alive. Switching SQL off by context and getting the same outcome (Assigned) means we still have a fallback if the kernel misbehaves.
- Quote the numbers carefully. The three columns are different stacks, so "SQL is Nx faster than ORM" on its own is misleading. Always state the baseline.
Still open
- This was a single run on one document. Repeated runs and behaviour under concurrent user load still need checking.
- Result correctness of the SQL kernel (quant, valuation, accounting entries) is a separate verification item from performance.
8. How to re-run
- Prod-clone ORM path β Settings β Non-POS Validate = ORM, or pass
gbstyle_disable_validate_full_state_sqlin the context. - Confirm the skip β look for
full-state SQL disabled by contextandnext=super_button_validatein the log. - Vanilla instance β start with
odoo19_envs\odoo_ctl.ps1 start vanilla, then openhttp://127.0.0.1:8029/web?db=odoo19_vanilla_ee.
Source material β full lab write-up: SQL_Path_vs_ORM_Performance_Comparison.md Β· raw 1,000-SKU chain results: sql_path_benchmark_chain_final_opt_1000c.json
Korean version: νΌνΉ Validate μ±λ₯ λΉκ΅ β Vanilla / SQL 컀λ / GBStyle ORM