Performance: fetch_order_trades query takes 1-2s per order hash on moderate databases

[alastairong1]

Problem

The fetch_order_trades SQL query in crates/common/src/local_db/query/fetch_order_trades/query.sql is very slow on databases with moderate data volume (~80MB, Base chain). A batch of 28 order hashes takes ~40 seconds, making trade lookups impractical for API use cases.

Root Causes

1. No connection pooling in RusqliteExecutor

RusqliteExecutor::query_json opens a fresh SQLite connection per query (open_connection() in executor.rs:54-65), including WAL mode setup, busy_timeout configuration, and custom function registration. When multiple queries run concurrently (e.g., fetching trades for multiple orders), each opens its own connection.

Suggestion: Use r2d2-sqlite or a similar pool, or cache a single connection per executor instance.

2. Missing composite indexes

The take_trades CTE joins take_orders to order_add_events on (chain_id, orderbook_address, order_owner, order_nonce), but idx_take_orders_owner only covers (chain_id, orderbook_address, order_owner) — missing order_nonce.

Similarly, the final vault_balance_changes JOIN matches on (owner, token, vault_id, block_number, log_index) but the closest index (idx_vbc_timestamp) is ordered by block_timestamp rather than block_number, log_index.

Suggestion: Add covering indexes:

CREATE INDEX idx_take_orders_owner_nonce 
  ON take_orders (chain_id, orderbook_address, order_owner, order_nonce);

CREATE INDEX idx_vbc_block_log 
  ON vault_balance_changes (chain_id, orderbook_address, owner, token, vault_id, block_number, log_index);

3. N+1 query pattern

There's no batch variant of fetch_order_trades — callers must loop over order hashes one at a time. A single query with WHERE order_hash IN (...) would eliminate redundant CTE evaluations and connection overhead.

Environment

• Database: ~80MB SQLite (Base chain, synced via local-db-sync)
• Concurrent queries: 28 order hashes via join_all
• Total wall time: ~40 seconds
• Per-query time: ~1.4 seconds average

Workaround

We're currently bypassing the library's trade query and running optimized SQL directly against the SQLite file with connection pooling and batch lookups.