Debug Slow SQL Queries with Dory
Slow query debugging works best when you isolate the expensive part before rewriting the full SQL. Reduce the scan range, validate joins, inspect aggregation and sorting, then ask AI for reviewable alternatives.
When to Use This Workflow
- SQL is slow or timing out.
- The query scans too much data.
- Joins, grouping, or ordering are expensive.
- ClickHouse Monitoring shows slow queries.
- AI-generated SQL is correct but too costly.
1. Locate the Slow Query
For ClickHouse, start with Monitoring:
- Check slow queries in the last hour or day.
- Inspect P95 latency and QPM.
- Filter by user, database, query type, or keyword.
- Copy the SQL into SQL Console.
For other databases, start with SQL Console duration, errors, and row counts.
2. Reduce the Data Range
SELECT ...
FROM your_table
WHERE event_time >= now() - INTERVAL 1 DAY
LIMIT 100;Check that the query has a time range, uses partition or sorting fields where possible, selects only needed columns, and avoids unbounded SELECT *.
3. Split Complex SQL
Validate each part independently:
- Base filters.
- Join input sizes.
- Aggregation logic.
- Final ordering or Top-N.
This helps identify whether filtering, joining, grouping, or sorting is the bottleneck.
4. Ask AI for a Performance Rewrite
This ClickHouse SQL is slow. Optimize it without changing the metric definition:
- only analyze the last 7 days
- avoid SELECT *
- use event_time filtering
- keep the final output columns
Explain why each change may be faster.5. Compare Before and After
Run both versions and compare duration, row count, metric values, scan scope, and maintainability. If results differ, investigate before adopting the optimized query.
Common Causes
| Cause | Fix |
|---|---|
| No time filter | Add a clear time range. |
| Too many columns | Select only required fields. |
| Join inputs too large | Filter or pre-aggregate before joining. |
| Too many group dimensions | Reduce dimensions or analyze in layers. |
| Sorting a large result | Aggregate or limit to Top-N first. |
FAQ
What if AI changes the result?
Do not adopt it. Compare filters, join type, deduplication, and aggregation grain.
What should I check first for ClickHouse?
Time range, partition keys, sorting keys, read rows, read bytes, and group dimensions.
When should I involve a database administrator?
Ask for DBA help when the query needs schema changes, index or partition changes, cluster tuning, or production workload coordination.
Next Steps
- Rewrite with Generate SQL with AI.
- Build visuals with Build Charts from SQL.
- Save proven queries in Saved Queries.