query-optimization

BigQuery Query Optimization

Parent Skill: /bigquery
Path: /bigquery/query-optimization

Purpose

Optimize BigQuery SQL queries for performance, cost efficiency, and prevent common pitfalls like data duplication, slow JOINs, and expensive aggregations.

When to Use

Trigger automatically when:

• User mentions: slow, optimize, performance, duplicate rows, expensive
• Query execution time > 10 seconds
• Bytes processed > 1GB
• User asks to "check my query"
• Unexpected row counts after JOINs

Chat commands:

/bigquery/query-optimization check my query "SELECT * FROM..."
/bigquery/query-optimization why is this slow "WITH ..."
/bigquery/query-optimization prevent duplicates in JOIN
/bigquery/query-optimization reduce costs for daily analytics

Requirements

Verification

# Ensure access to query stats
bq ls --project_id=your_project
bq show --format=prettyjson your_project:dataset.table

---

Critical Rules

---

Optimization Workflow

Step 1: Analyze Current Query

-- Get query execution stats
SELECT
  job_id,
  user_email,
  total_bytes_processed / POW(10,9) as gb_processed,
  total_slot_ms / 1000 as slot_seconds,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) as duration_sec,
  SUBSTR(query, 1, 100) as query_preview
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
ORDER BY total_bytes_processed DESC
LIMIT 10;