Query Optimization 🚀 — Making SQL Queries Faster

Query optimization is the process of improving a database query so that it returns the required result efficiently while using less CPU, memory, I/O, and database resources.

flowchart LR
    A["SQL Query"] --> O["Query Optimizer"]
    O --> P["Execution Plan"]
    P --> DB["Database"]
    DB --> R["Result"]

1. Query Execution Plan

The database optimizer determines how a query should be executed. The resulting execution plan shows operations such as index seeks, scans, joins, sorting, and filtering.

flowchart LR
    Q["Query"] --> P["Execution Plan"]

    P --> I["Index Seek"]
    P --> J["Join"]
    P --> F["Filter"]
    P --> S["Sort"]

    I --> R["Result"]

In SQL Server, you can inspect the Actual Execution Plan to understand how the query was actually executed.

2. Index Seek vs Table Scan

For a selective query with a suitable index, an index seek can locate the required rows efficiently.

flowchart LR
    Q["WHERE CustomerId = 101"] --> I["Index"]
    I --> R["Required Rows"]

A scan examines a larger portion of the table or index.

flowchart LR
    Q["Query"] --> S["Table / Index Scan"]
    S --> R["Many Rows Examined"]

3. Avoid SELECT *

Select only the columns required by the application.

-- Avoid when unnecessary
SELECT *
FROM Orders
WHERE CustomerId = 101;

-- Prefer required columns
SELECT OrderId, OrderDate, Amount
FROM Orders
WHERE CustomerId = 101;

This can reduce the amount of data read and transferred, and may allow the database to use narrower or covering indexes where appropriate.

4. Use Appropriate Indexes

Queries frequently filtering, joining, or sorting on particular columns may benefit from suitable indexes.

flowchart LR
    Q["Frequent Query"] --> A["Analyze WHERE / JOIN / ORDER BY"]
    A --> I["Suitable Index"]
    I --> R["Fewer Rows Examined"]

5. Avoid Functions on Indexed Columns When They Prevent Efficient Access

Applying a function to an indexed column can prevent the optimizer from using the index efficiently, depending on the database and expression.

-- Potentially problematic
SELECT *
FROM Orders
WHERE YEAR(OrderDate) = 2026;

A range predicate can often be more index-friendly:

SELECT *
FROM Orders
WHERE OrderDate >= '2026-01-01'
  AND OrderDate < '2027-01-01';

6. Avoid Returning Unnecessary Rows

Filtering early and using pagination for large result sets can reduce unnecessary work.

SELECT OrderId, OrderDate, Amount
FROM Orders
WHERE CustomerId = 101
ORDER BY OrderDate DESC
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;

7. Join Optimization

Joins should be supported by appropriate keys and indexes where the workload requires them.

flowchart LR
    C["Customer"] --> J["JOIN"]
    O["Order"] --> J
    J --> R["Result"]

    C --> I1["CustomerId Index"]
    O --> I2["CustomerId Index"]

8. N+1 Query Problem

An application can accidentally execute one query to retrieve a list and then another query for every item in that list.

flowchart LR
    A["Application"] --> Q1["Query Customers"]

    Q1 --> Q2["Query Order 1"]
    Q1 --> Q3["Query Order 2"]
    Q1 --> Q4["Query Order 3"]
    Q1 --> QN["Query Order N"]

This can create a large number of database calls. Depending on the use case, a join, batch query, projection, or other data-access strategy can reduce the number of round trips.

9. Statistics and Execution Plans

Query optimizers use statistics and other metadata to estimate row counts and choose execution strategies.

flowchart LR
    Q["SQL Query"] --> O["Optimizer"]
    S["Statistics"] --> O
    I["Indexes"] --> O

    O --> P["Execution Plan"]
    P --> DB["Execution"]

When a query becomes unexpectedly slow, inspect the execution plan, estimated versus actual row counts, indexes, joins, and expensive operators.

10. Query Optimization Checklist

• Check the execution plan.
• Check whether appropriate indexes exist.
• Return only required columns.
• Filter efficiently.
• Check expensive joins and sorts.
• Look for unnecessary database round trips.
• Check for N+1 queries.
• Review statistics and data distribution.
• Measure before and after the change.

Important Interview Questions

• What is query optimization?
• What is an execution plan?
• Index Seek vs Index/Table Scan?
• How would you troubleshoot a slow SQL query?
• Why should we avoid SELECT *?
• How can functions on columns affect index usage?
• What is the N+1 query problem?
• How do indexes affect query performance?
• What are statistics used for?
• How would you optimize a query returning millions of rows?

Key Takeaways

• Execution Plan → understand how the database executes the query.
• Indexes → reduce unnecessary data access for suitable queries.
• Filtering → avoid processing unnecessary rows.
• Projection → return only required columns.
• N+1 → avoid unnecessary repeated database calls.
• Always measure query performance before and after optimization.

Conclusion

For interviews, remember: Slow Query → Execution Plan → Find Bottleneck → Optimize → Measure Again.