Query Optimizations
No Optimizations Yet
You haven't optimized any queries yet. Submit your first query to see how Reinspect can improve its performance.
Optimize Your First QueryOptimization History
| Query | Original Time | Optimized Time | Improvement | Date | Actions |
|---|---|---|---|---|---|
SELECT * FROM customer_orders WHERE order_date > '2023-01-01' |
45.3s | 12.1s | 73.3% | Oct 15, 2023 | |
SELECT customer_id, SUM(amount) FROM transactions GROUP BY customer_id |
78.2s | 31.5s | 59.7% | Oct 14, 2023 | |
SELECT p.product_name, c.category_name FROM products p JOIN categories c ON p.category_id = c.id
|
32.7s | 8.4s | 74.3% | Oct 13, 2023 | |
SELECT * FROM sales WHERE region = 'Northeast' AND date BETWEEN '2023-01-01' AND '2023-06-30'
|
28.5s | 9.2s | 67.7% | Oct 12, 2023 | |
SELECT d.department_name, COUNT(e.employee_id) FROM employees e JOIN departments d ON e.department_id = d.id GROUP BY d.department_name
|
15.3s | 6.8s | 55.6% | Oct 11, 2023 |
Optimization Details
Query Information
Original Query:
SELECT * FROM customer_orders WHERE order_date > '2023-01-01'
Database: SNOWFLAKE_DB
Schema: SALES
Optimization Date: October 15, 2023
Optimization Results
Execution Time
45.3s → 12.1s
73.3% fasterData Scanned
2.3TB → 0.6TB
73.9% lessKey Improvements
- Added partition pruning on date column
- Optimized join order
- Added appropriate filters
SELECT *
FROM customer_orders
WHERE order_date > '2023-01-01'SELECT co.order_id, co.customer_id, co.order_date, co.total_amount, co.status
FROM customer_orders co
WHERE co.order_date > '2023-01-01'
ORDER BY co.order_dateOptimization Explanation
The original query was optimized in several ways:
- Explicit Column Selection: Instead of using SELECT *, the optimized query explicitly lists the columns needed. This reduces the amount of data that needs to be transferred and processed.
- Table Aliasing: Added table alias 'co' for better readability and to prepare for potential future joins.
- Added ORDER BY: Added an explicit ORDER BY clause on the date column, which can leverage the existing index on order_date.
These optimizations resulted in a 73.3% reduction in execution time and a 73.9% reduction in data scanned.
Additional Recommendations
- Consider creating a materialized view for frequently accessed recent orders
- Add a clustering key on order_date if not already present
- Consider partitioning the table by year and month for better query performance
Performance Comparison
| Metric | Original Query | Optimized Query | Improvement |
|---|---|---|---|
| Execution Time | 45.3 seconds | 12.1 seconds | 73.3% |
| Compilation Time | 1.2 seconds | 0.8 seconds | 33.3% |
| Data Scanned | 2.3 TB | 0.6 TB | 73.9% |
| Rows Returned | 1,245,678 | 1,245,678 | 0% |
| Partitions Scanned | 24 | 6 | 75% |
| Credits Used | 1.2 | 0.3 | 75% |
Execution Plan Comparison
Original Query Plan
TableScan [customer_orders]
Filter [order_date > '2023-01-01']
OutputOptimized Query Plan
TableScan [customer_orders]
PartitionPruning [order_date > '2023-01-01']
ProjectColumns [order_id, customer_id, order_date, total_amount, status]
Sort [order_date ASC]
Output