Optimize Query from SQL Text
OUTPUT FORMAT
Return ONLY the optimized SQL query. No markdown formatting, no explanations, no bullet points - just pure SQL that can be executed directly in Snowflake.
CRITICAL: Semantic Preservation Rules
The optimized query MUST return IDENTICAL results to the original.
Before returning ANY optimization, verify:
- Same columns: Exact same columns in exact same order with exact same aliases
- Same rows: Filter conditions must be semantically equivalent
- Same ordering: Preserve
ORDER BY exactly as written
- Same limits: If original has
LIMIT N, keep LIMIT N. If no LIMIT, do NOT add one.
If you cannot guarantee identical results, return the original query unchanged.
Pattern 1: Function on Filter Column
Problem: Functions on columns in WHERE clause prevent partition pruning and index usage.
CAN Fix
| Original |
Optimized |
Why Safe |
WHERE DATE(ts) = '2024-01-01' |
WHERE ts >= '2024-01-01' AND ts < '2024-01-02' |
Equivalent range |
WHERE YEAR(dt) = 2024 |
WHERE dt >= '2024-01-01' AND dt < '2025-01-01' |
Equivalent range |
WHERE MONTH(dt) = 3 AND YEAR(dt) = 2024 |
WHERE dt >= '2024-03-01' AND dt < '2024-04-01' |
Equivalent range |
WHERE DATE(ts) >= '2024-01-01' AND DATE(ts) < '2024-02-01' |
WHERE ts >= '2024-01-01' AND ts < '2024-02-01' |
Same boundaries |
WHERE YEAR(dt) BETWEEN 1995 AND 1996 |
WHERE dt >= '1995-01-01' AND dt < '1997-01-01' |
Equivalent range |
CANNOT Fix
| Pattern |
Why Not |
WHERE YEAR(dt) IN (SELECT year FROM ...) |
Dynamic values, cannot precompute range |
WHERE DATE(ts) = DATE(other_col) |
Comparing two columns, both need function |
WHERE EXTRACT(DOW FROM dt) = 1 |
Day-of-week has no contiguous range |
WHERE DATE_TRUNC('month', dt) = '2024-01-01' in GROUP BY |
Needed for grouping logic |
SELECT YEAR(dt) AS yr ... GROUP BY YEAR(dt) |
Function in SELECT/GROUP BY is fine, only filter matters |
Pattern 2: Function on JOIN Column
Problem: Functions on JOIN columns prevent hash joins, forcing slower nested loop joins.
CAN Fix
| Original |
Optimized |
Why Safe |
ON CAST(a.id AS VARCHAR) = CAST(b.id AS VARCHAR) |
ON a.id = b.id |
If both are same type (e.g., INTEGER) |
ON UPPER(a.code) = UPPER(b.code) |
ON a.code = b.code |
If data is already consistently cased |
ON TRIM(a.name) = TRIM(b.name) |
ON a.name = b.name |
If data has no leading/trailing spaces |
CANNOT Fix
| Pattern |
Why Not |
ON CAST(a.id AS VARCHAR) = b.string_id |
Types genuinely differ, CAST required |
ON DATE(a.timestamp) = b.date_col |
Different granularity, DATE() required |
ON UPPER(a.code) = b.code |
If b.code might have different case |
ON a.id = b.id + 1 |
Arithmetic transformation, cannot remove |
Pattern 3: NOT IN Subquery
Problem: NOT IN has poor performance and unexpected NULL behavior.
CAN Fix
| Original |
Optimized |
Why Safe |
WHERE id NOT IN (SELECT id FROM t WHERE ...) |
WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = main.id AND ...) |
Equivalent when subquery column is NOT NULL |
WHERE id NOT IN (SELECT id FROM t) where id has NOT NULL constraint |
WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = main.id) |
NOT NULL guarantees equivalence |
CANNOT Fix
| Pattern |
Why Not |
WHERE id NOT IN (SELECT nullable_col FROM t) |
If subquery returns NULL, NOT IN returns no rows; NOT EXISTS doesn't |
WHERE (a, b) NOT IN (SELECT x, y FROM t) |
Multi-column NOT IN has complex NULL semantics |
Key Rule: Only convert NOT IN to NOT EXISTS if you can verify the subquery column cannot be NULL.
Pattern 4: Repeated Subquery
Problem: Same subquery executed multiple times causes redundant scans.
CAN Fix
| Original |
Optimized |
| Subquery appears 2+ times identically |
Extract to CTE, reference CTE multiple times |
| Same aggregation used in multiple places |
Compute once in CTE |
CANNOT Fix
| Pattern |
Why Not |
| Correlated subquery (references outer table) |
Each execution is different, cannot cache |
| Subqueries with different filters |
Not actually the same subquery |
| Subquery in SELECT that depends on current row |
Correlation prevents extraction |
Pattern 5: Implicit Comma Joins
Problem: Comma-separated tables in FROM clause are harder to read and optimize.
CAN Fix - Always
Convert FROM a, b, c WHERE a.id = b.id AND b.id = c.id to explicit JOIN syntax.
This is always safe - just restructuring, no semantic change.
UNSAFE Optimizations (NEVER apply)
- UNION to UNION ALL: UNION deduplicates rows, UNION ALL does not - different results
- Changing window functions: Do not modify
SUM(SUM(x)) OVER(...) or similar nested aggregates
- Adding redundant filters: Do not add filters in JOIN ON if same filter exists in WHERE
- Changing column names: Copy column names EXACTLY from original - do not "simplify" or rename
- Changing column aliases: Keep all aliases exactly as original
- Adding early filtering in JOINs: If a filter is in WHERE, do not duplicate it in JOIN ON clause
Principles
- Minimal changes: Make the fewest changes necessary. Simpler optimizations are more reliable.
- Preserve structure: Keep subqueries, CTEs, and overall query structure unless there's a clear benefit.
- When in doubt, don't: If unsure whether a change preserves semantics, skip it.
- Copy exactly: Column names, table aliases, and expressions should be copied character-for-character.
Priority Order
- Date/time functions on filter columns - Highest impact
- Implicit joins to explicit JOIN - Always safe, improves readability
- NOT IN to NOT EXISTS - Only if NULL-safe
Requirements
- Results must be identical: Same rows, same columns, same order
- Valid Snowflake SQL: Output must execute without errors in Snowflake
1---2name: optimizing-query-text3description: Optimizes Snowflake SQL query performance from provided query text. Use when optimizing Snowflake SQL for: (1) User provides or pastes a SQL query and asks to optimize, tune, or improve it (2) Task mentions "slow query", "make faster", "improve performance", "optimize SQL", or "query tuning" (3) Reviewing SQL for performance anti-patterns (function on filter column, implicit joins, etc.) (4) User asks why a query is slow or how to speed it up4---5
6# Optimize Query from SQL Text
7
8## OUTPUT FORMAT
9
10Return ONLY the optimized SQL query. No markdown formatting, no explanations, no bullet points - just pure SQL that can be executed directly in Snowflake.
11
12## CRITICAL: Semantic Preservation Rules
13
14**The optimized query MUST return IDENTICAL results to the original.**
15
16Before returning ANY optimization, verify:
17- **Same columns**: Exact same columns in exact same order with exact same aliases
18- **Same rows**: Filter conditions must be semantically equivalent
19- **Same ordering**: Preserve `ORDER BY` exactly as written
20- **Same limits**: If original has `LIMIT N`, keep `LIMIT N`. If no LIMIT, do NOT add one.
21
22**If you cannot guarantee identical results, return the original query unchanged.**
23
24---
25
26## Pattern 1: Function on Filter Column
27
28**Problem**: Functions on columns in WHERE clause prevent partition pruning and index usage.
29
30### CAN Fix
31
32| Original | Optimized | Why Safe |
33|----------|-----------|----------|
34| `WHERE DATE(ts) = '2024-01-01'` | `WHERE ts >= '2024-01-01' AND ts < '2024-01-02'` | Equivalent range |
35| `WHERE YEAR(dt) = 2024` | `WHERE dt >= '2024-01-01' AND dt < '2025-01-01'` | Equivalent range |
36| `WHERE MONTH(dt) = 3 AND YEAR(dt) = 2024` | `WHERE dt >= '2024-03-01' AND dt < '2024-04-01'` | Equivalent range |
37| `WHERE DATE(ts) >= '2024-01-01' AND DATE(ts) < '2024-02-01'` | `WHERE ts >= '2024-01-01' AND ts < '2024-02-01'` | Same boundaries |
38| `WHERE YEAR(dt) BETWEEN 1995 AND 1996` | `WHERE dt >= '1995-01-01' AND dt < '1997-01-01'` | Equivalent range |
39
40### CANNOT Fix
41
42| Pattern | Why Not |
43|---------|---------|
44| `WHERE YEAR(dt) IN (SELECT year FROM ...)` | Dynamic values, cannot precompute range |
45| `WHERE DATE(ts) = DATE(other_col)` | Comparing two columns, both need function |
46| `WHERE EXTRACT(DOW FROM dt) = 1` | Day-of-week has no contiguous range |
47| `WHERE DATE_TRUNC('month', dt) = '2024-01-01'` in GROUP BY | Needed for grouping logic |
48| `SELECT YEAR(dt) AS yr ... GROUP BY YEAR(dt)` | Function in SELECT/GROUP BY is fine, only filter matters |
49
50---
51
52## Pattern 2: Function on JOIN Column
53
54**Problem**: Functions on JOIN columns prevent hash joins, forcing slower nested loop joins.
55
56### CAN Fix
57
58| Original | Optimized | Why Safe |
59|----------|-----------|----------|
60| `ON CAST(a.id AS VARCHAR) = CAST(b.id AS VARCHAR)` | `ON a.id = b.id` | If both are same type (e.g., INTEGER) |
61| `ON UPPER(a.code) = UPPER(b.code)` | `ON a.code = b.code` | If data is already consistently cased |
62| `ON TRIM(a.name) = TRIM(b.name)` | `ON a.name = b.name` | If data has no leading/trailing spaces |
63
64### CANNOT Fix
65
66| Pattern | Why Not |
67|---------|---------|
68| `ON CAST(a.id AS VARCHAR) = b.string_id` | Types genuinely differ, CAST required |
69| `ON DATE(a.timestamp) = b.date_col` | Different granularity, DATE() required |
70| `ON UPPER(a.code) = b.code` | If b.code might have different case |
71| `ON a.id = b.id + 1` | Arithmetic transformation, cannot remove |
72
73---
74
75## Pattern 3: NOT IN Subquery
76
77**Problem**: `NOT IN` has poor performance and unexpected NULL behavior.
78
79### CAN Fix
80
81| Original | Optimized | Why Safe |
82|----------|-----------|----------|
83| `WHERE id NOT IN (SELECT id FROM t WHERE ...)` | `WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = main.id AND ...)` | Equivalent when subquery column is NOT NULL |
84| `WHERE id NOT IN (SELECT id FROM t)` where id has NOT NULL constraint | `WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = main.id)` | NOT NULL guarantees equivalence |
85
86### CANNOT Fix
87
88| Pattern | Why Not |
89|---------|---------|
90| `WHERE id NOT IN (SELECT nullable_col FROM t)` | If subquery returns NULL, NOT IN returns no rows; NOT EXISTS doesn't |
91| `WHERE (a, b) NOT IN (SELECT x, y FROM t)` | Multi-column NOT IN has complex NULL semantics |
92
93**Key Rule**: Only convert NOT IN to NOT EXISTS if you can verify the subquery column cannot be NULL.
94
95---
96
97## Pattern 4: Repeated Subquery
98
99**Problem**: Same subquery executed multiple times causes redundant scans.
100
101### CAN Fix
102
103| Original | Optimized |
104|----------|-----------|
105| Subquery appears 2+ times identically | Extract to CTE, reference CTE multiple times |
106| Same aggregation used in multiple places | Compute once in CTE |
107
108### CANNOT Fix
109
110| Pattern | Why Not |
111|---------|---------|
112| Correlated subquery (references outer table) | Each execution is different, cannot cache |
113| Subqueries with different filters | Not actually the same subquery |
114| Subquery in SELECT that depends on current row | Correlation prevents extraction |
115
116---
117
118## Pattern 5: Implicit Comma Joins
119
120**Problem**: Comma-separated tables in FROM clause are harder to read and optimize.
121
122### CAN Fix - Always
123
124Convert `FROM a, b, c WHERE a.id = b.id AND b.id = c.id` to explicit JOIN syntax.
125
126This is always safe - just restructuring, no semantic change.
127
128---
129
130## UNSAFE Optimizations (NEVER apply)
131
132- **UNION to UNION ALL**: UNION deduplicates rows, UNION ALL does not - different results
133- **Changing window functions**: Do not modify `SUM(SUM(x)) OVER(...)` or similar nested aggregates
134- **Adding redundant filters**: Do not add filters in JOIN ON if same filter exists in WHERE
135- **Changing column names**: Copy column names EXACTLY from original - do not "simplify" or rename
136- **Changing column aliases**: Keep all aliases exactly as original
137- **Adding early filtering in JOINs**: If a filter is in WHERE, do not duplicate it in JOIN ON clause
138
139---
140
141## Principles
142
1431. **Minimal changes**: Make the fewest changes necessary. Simpler optimizations are more reliable.
1442. **Preserve structure**: Keep subqueries, CTEs, and overall query structure unless there's a clear benefit.
1453. **When in doubt, don't**: If unsure whether a change preserves semantics, skip it.
1464. **Copy exactly**: Column names, table aliases, and expressions should be copied character-for-character.
147
148---
149
150## Priority Order
151
1521. **Date/time functions on filter columns** - Highest impact
1532. **Implicit joins to explicit JOIN** - Always safe, improves readability
1543. **NOT IN to NOT EXISTS** - Only if NULL-safe
155
156---
157
158## Requirements
159
160- **Results must be identical**: Same rows, same columns, same order
161- **Valid Snowflake SQL**: Output must execute without errors in Snowflake
majiayu000