Query optimization improves a database query’s efficiency while preserving its intended results. AI assistance can make this demanding DBA task faster by analyzing SQL and execution plans, identifying bottlenecks, and suggesting targeted rewrites. section 10.2 section 10.0
A practical optimization workflow includes:
- Provide the query, table schema, existing indexes, and execution plan; identify each item clearly. section 10.2
- Ask the chatbot to wait until all required information arrives, then request bottleneck analysis and an optimized query. section 10.2
- Validate every suggested rewrite for both performance and accuracy before using it. section 10.2
Performance troubleshooting prompt
“I have a query that is performing poorly. I will provide the query, followed by the execution plan. Do not respond until you have both. Analyze the data, suggest performance improvements, and provide an optimized query.” Follow with the SQL and execution plan. Typical recommendations include removing unnecessary joins and using indexes.
Table 10.2 Query optimization prompts
|
Task
|
Prompt
|
Response summary
|
|---|---|---|
|
Query performance troubleshooting (part 1)
|
I have a query that is performing poorly in my database. I will first provide the query, followed by the execution plan. Do not respond until you have been provided both. Analyze the data and make suggestions on how to boost performance. Provide an optimized query incorporating the recommendations.
Here is the query
[Query SQL]
|
The chatbot accepts the initial prompt and query. As instructed, it waits for the execution plan to be provided.
|
|
Query performance troubleshooting (part 2)
|
Here is the execution plan
[Execution plan]
|
The chatbot waits for all necessary information and then analyzes the query and execution plan. The chatbot identifies the complexity of the query and several bottlenecks in the execution plan. The chatbot recommends removing unnecessary joins, using indexes, avoiding functions affecting index usage, avoiding sorting, and using
IN with subqueries. An optimized query is provided for testing.
|
|
Index suggestions
|
Analyze this query and make suggestions on which fields should have indexes. [Query SQL]
|
The chatbot analyzes the query’s
JOIN and
WHERE clauses to make suggestions about which fields should be indexed.
|
|
Code review
|
I was asked to perform a code review on a new SQL statement. Analyze the statement, looking for any opportunities to improve the query, possible logic errors and security concerns. If you identify any issues, please provide constructive feedback on why the changes should be implemented. I have also provided a list of requirements my team has established as standard practice.
1. Avoid table variables
2. Include
SET NOCOUNT ON
[Continued list of standards]
[Query SQL]
|
The query is analyzed as requested, and recommendations are made regarding readability and maintainability, performance, logic, security, and clarity. Most notably, the
JOIN syntax is validated, index recommendations are made, and custom functions are identified. The security considerations (SQL injection attacks and hardcoded values) are discussed. If query code violates the list of standards, those items are denoted.
|
Browse the prompt table for reusable query troubleshooting prompts and response patterns. section 10.2
