SQL Performance Copilot
Optimizing the performance and cost of SQL queries has never been easier. With Waii’s SQL Performance Copilot, you can leverage state-of-the-art AI capabilities and a highly curated knowledge base of performance-tuning techniques to swiftly identify and address expensive queries in your application. Not an SQL performance expert? No worries. Waii has you covered.
What does it do?
- Helps you discover expensive queries quickly.
- Analyzes performance metrics in depth for you.
- Helps you apply best practice recommendations to optimize your SQL performance.
Step 1: Identify Expensive Queries Using Natural Language 🗣
The layout of performance information in a database’s system tables can be confusing. With Waii you can simply use English to ask questions system’s performance. This lets you identify expensive queries with ease.
Example:
You ask: "Find the 3 most expensive queries in the last 24 hours ranked on execution time."
Waii responds with a detailed table:
QUERY_ID | QUERY_TEXT | DATABASE_NAME | ... | EXECUTION_TIME | ...
--------------------------------------------------------------------------
01af97f... | WITH spend AS... | TWEAKIT_PERF_DB | ... | 72607 ms | ...
01af9a3... | select s_store_name... | TWEAKIT_PERF_DB | ... | 61409 ms | ...
01af9a1... | SELECT query_id... | SNOWFLAKE | ... | 27563 ms | ...
Step 2: Get a Detailed Performance Summary with One Click
A comprehensive performance summary provides insights on compile times, runtimes, and resource consumption metrics of your SQL queries.
Example:
PerformanceSummary
- Compile time: 1017 ms
- Runtime: 61409 ms
- The operator 'tablescan' on the 'store_sales' table is the most expensive, scanning 3.1 TB of data with 0% from cache.
- The 'aggregate' operator also consumes significant resources, processing 383 million rows and spilling 879 MB of data to local storage.
- The repeated tablescan on the 'store_sales' table also contributes to the high resource usage, scanning 3.1 TB of data with 79% from cache.
Step 3: Implement Performance Recommendations
Based on a thorough analysis of your queries, the SQL Performance Copilot suggests actionable recommendations to enhance performance.
Example:
Performance Recommendations:
- Reduce the repeated table scans on 'store_sales'. Combine the two subqueries that scan the 'store_sales' table into one, storing the result in a temporary table.
- Push down the filter condition 'ss_sold_date_sk = d_date_sk and d_month_seq between 1176 and 1176+11' to the 'store_sales' table scan.
- Remove the unnecessary 'group by' clause in the subquery 'sb'.
- Push down the filter condition 'sc.revenue <= 0.1 * sb.ave' to the 'store_sales' table scan.
- Replace the implicit JOIN syntax with explicit JOIN syntax for better query readability and optimization.
Optimizing cost and performance is critical in today's data world. Waii dramatically simplifies the task and opens up this capability to non-performance experts. Happy optimizing!