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 BY clause 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 WHERE clause
    • 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