Query Optimization
- Common reasons for slow queries:
- sorts
- unfiltered data
- querying a large number of records
- Be specific in selecting what is needed
- adding more than what is needed can slow the query
- Tools can support the discovery and correction of objects, like queries, that are causing performance issues
- e.g., Azure SQL Automatic Tuning, AWS RDS Performance Insights
Removing Sorts
- including the
ORDER BYclause in a query that pulls hundreds of thousands of records is going to slow the query down - if the sort can be performed elsewhere, it should be
Filtering
- Can improve performance by filtering data using the
WHEREclause- reduces the number of records pulled, as long as it still satisfies the objective
Query Execution Plan
A query execution plan is the order of steps in which a query is processed.
- is a visual representation that provides details about how the query executes
- includes:
- an estimated execution plan of possible requirements for executing the query
- the actual execution plan that is known once the query has been executed
- estimated execution plan can reveal areas of high server usage to process the information