πŸ“Š 2026-09-19 Picking Validate Performance β€” Vanilla / SQL kernel / GBStyle ORM
πŸ“Š

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

  1. How much faster is the SQL kernel in practice?
  2. 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.

PathWhat actually ranEnvironment
Vanilla ORMCore Odoo button_validate onlyInstance odoo19_vanilla_ee, port 8029. No bss_gbstyle addons
Prod-clone SQLvalidate_full_state_sql kernel in bss_gbstyle_quant_impProd-clone DB and code. Set-based quant / state
Prod-clone ORMSame clone, SQL kernel switched offgbstyle_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 ORMProd-clone SQLProd-clone ORM
DocumentKCWH/PICK/00004…/OUT/50375…/OUT/50424
Lines1,4981,5001,494
Validate time49s21s81s
Queries28,7187,81279,325
Per line~19~5~53
Next stepAssignedAssignedAssigned

Ratios β€” same shape, different stack:

ComparisonTimeQueries
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.move state write in bss_gbstyle_main re-runs putaway and may write location_final_id again.
  • Stored picking computes, Cafe24 button_validate pre/post, branch ACL, chatter, downstream assign.
  • A fat stock_quant, real-time valuation and ir.rule make 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.

PhaseORM (ms)Final SQL (ms)Faster
step1 confirm84,7911,47298.3%
step1 validate + push103,50510,76389.6%
step2 assign88,84978899.1%
step2 validate + push72,5279,29487.2%
step3 assign50,59979298.4%
step3 validate19,7711,98790.0%
Hot path total420,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 state write 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

  1. Prod-clone ORM path β€” Settings β†’ Non-POS Validate = ORM, or pass gbstyle_disable_validate_full_state_sql in the context.
  2. Confirm the skip β€” look for full-state SQL disabled by context and next=super_button_validate in the log.
  3. Vanilla instance β€” start with odoo19_envs\odoo_ctl.ps1 start vanilla, then open http://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