Optimizing SQL Queries: Fundamentals and Strategies

TLDR

1. Fetch only the required columns to minimize data transfer.

2. Use stored procedures for reusable and precompiled queries.

3. Filter data with CTEs before joining large tables.

4. Leverage clustered and non-clustered indexes strategically.

5. Ensure the column order in the WHERE clause matches the index column order to improve query performance.

6. Analyze execution plans to identify inefficiencies.

7. Monitor and address index fragmentation regularly.

I learned these optimization techniques through hands-on experience while optimising my APIs. I realized the significant impact indexing, column order in the query and addressing fragmentation can have on performance.

Try brainstorming with ChatGPT to dive into the implementation details of any section.

I tried to keep things simple and brief.

Query Flow Overview:

Understanding how queries flow through a system can reveal opportunities for optimization. The typical query process looks like this:

1. Application Layer: The query originates from an application and is sent to the database server via a network call.

2. Database Server:

1. Parse the query: Interpret and validate the query.

2. Optimize the query: Create the best execution plan using metadata and statistics.

3. Retrieve data from storage: Fetch the required data and return it to the application.

At each stage, follow these simple ways to improve efficiency, reduce latency, and optimize resource usage.

Optimization Strategies

1. Fetch Only the Required Data:

Minimize the data retrieved from the database by selecting only the necessary columns. This reduces data transfer time, saves bandwidth, and improves maintainability.

-- Bad: Fetching all columns

SELECT * FROM employees;

-- Better: Selecting only required columns

SELECT id, name, salary FROM employees;

Why?

1. Reduces the amount of data transferred.

2. Makes the query easier to debug and maintain.

3. Prevents unintended side effects if new columns are added.

2. Use Stored Procedures for Reusability:

Stored procedures allow precompiled queries to be executed efficiently, saving time by avoiding redundant parsing and optimization.

CREATE PROCEDURE GetUserPermissions @userID INT
AS
BEGIN

    SELECT id, permission_name

    FROM user_permissions

    WHERE user_id = @userID;

END;

-- Execute the procedure

EXEC GetUserPermissions 101;

Why?

1. Avoids repetitive parsing and optimization.

2. Promotes code reuse.

3. Allows parameterization for flexibility.

3. Filter Data Early with Common Table Expressions (CTEs):

CTEs allow you to filter data before performing complex operations like joins. This reduces the memory and computation required during query execution.

-- Without CTE: Joins all rows before filtering

SELECT e.id, e.name, d.department_name

FROM employees e

JOIN departments d

ON e.department_id = d.id;


-- With CTE: Filters data first

WITH FilteredEmployees AS (

    SELECT id, name, department_id

    FROM employees

    WHERE salary > 50000

)

SELECT f.id, f.name, d.department_name

FROM FilteredEmployees f

JOIN departments d

ON f.department_id = d.id;

Why?

1. Reduces the number of rows processed in joins.

2. Improves query performance by minimizing unnecessary data loading in memory

4. Indexing for Faster Data Retrieval:

Indexes are data structures that improve query speed by organizing data for efficient access. Understanding how and when to use indexes is crucial.

How Data Is Stored:

Data in a database is stored in blocks on disk. Each block contains multiple rows. When a query retrieves data, the database reads these blocks into memory. Efficient storage and retrieval are essential for performance.

Types of Indexes:

Clustered Index:

1. Determines the physical order of data in a table.

2. Directly points to the data, making retrieval faster.

3. Primary Key automatically creates a clustered index.

4. if a table has a clustered index on id , the rows will be physically stored in the order of id.

CREATE CLUSTERED INDEX idx_employee_id

  ON employees(id);

Non-Clustered Index:

1. Creates a separate internal table for indexed columns pointing to the data blocks

2. Useful for frequently queried columns that aren’t part of the clustered index.

 CREATE NONCLUSTERED INDEX idx_employee_name

  ON employees(name);

Trade-offs:

- Clustered indexes are faster for retrieval but can slow down insert/update operations.

- Non-clustered indexes can speed up specific queries but require additional storage and also slow down the insert/update operations.

Fragmentation occurs when rows are scattered across blocks due to updates or deletions, leaving unused spaces. This disorganization increases the number of blocks the database must read, slowing down queries.

5. Align Queries with Index Order:

When querying indexed tables, the order of columns in the query should match the index order for optimal performance.

-- Inefficient: Order doesn’t match the index
SELECT * FROM employees WHERE name = 'John' AND id = 101;

-- Efficient: Matches the index order

SELECT * FROM employees WHERE id = 101 AND name = 'John'

Why?

• Mismatched orders force the database to scan the entire index, reducing efficiency.

6. Analyze Execution Plans:

Execution plans help identify inefficiencies in queries. Use them to verify if indexes are being utilized correctly.

Check Index Usage:

- SQL Server:

 SET SHOWPLAN_XML ON;
 SELECT * FROM employees WHERE id = 101

- MySQL Server:

EXPLAIN SELECT * FROM employees WHERE id = 101;

Key Terms to Look For:

- Index Seek: Fastest, uses the index to locate rows.

- Index Scan: Slower, scans the entire index.

- Table Scan: Slowest, scans the entire table.

7. Monitor and Address Fragmentation:

Fragmentation occurs when data in indexed tables becomes disorganized, reducing query performance. Regular maintenance ensures indexes remain efficient.

How to Handle Fragmentation:

- Reorganize Index: For moderate fragmentation (< 30%). Rearrange data without a full rebuild.

-- Reorganize an index
ALTER INDEX idx_employee_id ON employees REORGANIZE;

- Rebuild Index: For high fragmentation (> 30%). Completely restructures the index.

-- Rebuild an index
ALTER INDEX idx_employee_id ON employees REBUILD;

Why?

- Reorganizing is faster and less resource-intensive.

- Rebuilding is necessary for highly fragmented indexes or schema changes.

Balancing Indexing and Write Performance:

Indexes improve read performance but can slow down writes (inserts, updates, deletes) because they need to maintain additional data structures. Strike a balance based on your workload:

- Prioritize indexing for columns used in frequent queries.

- Avoid excessive indexing to minimize write overhead.

By applying these principles, you can significantly improve query performance, reduce resource consumption, and create a robust database system.