Skip to content

← Training overview

3. Query optimization

Process views are SQL — and SQL can be slow. The query optimizer generates several rewrite candidates for a registered process, scores each one against the original via the Teradata EXPLAIN plan, and (optionally) replaces the view with the best candidate. It is also exposed as a one-flag inline option on upload_features.

Inline on upload

tdfs4ds.upload_features(
    df=df,
    entity_id=['customer_id'],
    feature_names=['lifetime_amount'],
    optimize_query=True,      # generate + score rewrite candidates
    update_view=True,         # replace the view DDL with the best candidate
    max_candidates=5,         # override QUERY_OPTIMIZER_MAX_CANDIDATES
)

Without an instruct model configured the flag is silently ignored, so it is safe to keep on in code that runs in environments that may or may not have an LLM available.

Out-of-band

from tdfs4ds.ai import skill_optimize_query

report = skill_optimize_query(process_id=process_id, update_view=False)
print(report['score_delta'], report['best_sql'])

The full Markdown report — original vs each candidate, EXPLAIN signals, spool counts — is saved under {QUERY_OPTIMIZER_REPORT_DIR}/{DATA_DOMAIN}/tdfs4ds_optim_{view}_{process_id}.md.

How it scores

Four signals are persisted per process:

  • user_score (1–5) — LLM-derived SQL quality (the part the author controls).
  • global_score (1–5) — LLM-derived plan quality, including infra factors.
  • n_steps — deterministic count of execution steps.
  • n_spool_objects — deterministic count of intermediate spool objects.

Improvement is judged on these four together, not on the LLM's opinion alone.

When to tune the knobs

  • QUERY_OPTIMIZER_MAX_CANDIDATES (default 10) — lower it for short feedback loops, raise it when you want the optimizer to explore more aggressively.
  • QUERY_OPTIMIZER_MAX_CANDIDATE_TOKENS — cap the output length for small-context models (≤ 16 k) so they don't exhaust their budget on prose.

Learn more