10 SQL Optimization Interview Questions and Answers

SQL optimization is a critical skill for managing and querying databases efficiently. As data volumes grow and applications become more complex, the ability to write optimized SQL queries can significantly impact performance and resource utilization. Mastering SQL optimization techniques is essential for ensuring that databases run smoothly and can handle increasing demands without compromising speed or reliability.

This article provides a curated selection of SQL optimization questions and answers to help you prepare for your upcoming interview. By understanding these concepts and practicing the provided examples, you will be better equipped to demonstrate your proficiency in optimizing SQL queries and managing database performance effectively.

SQL Optimization Interview Questions and Answers

1. How would you create an index on a table with millions of rows to improve query performance?

Creating an index on a table with millions of rows can enhance query performance by allowing the database to quickly locate data without scanning the entire table. Indexes are particularly useful for columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses. Types of indexes include single-column, composite, unique, and full-text indexes. When creating an index, consider that while it can improve read performance, it may slow down write operations. Choose columns that are frequently queried and monitor the index’s performance impact.

Example:

CREATE INDEX idx_column_name ON table_name (column_name);

For a composite index:

CREATE INDEX idx_composite ON table_name (column1, column2);

2. Discuss the trade-offs between normalization and denormalization in terms of query performance.

Normalization organizes data to reduce redundancy and improve integrity, dividing large tables into smaller, related ones. Denormalization combines tables to improve read performance by reducing joins. The trade-offs include:

• Normalization:
  • Improves data integrity and consistency.
  • Reduces data anomalies.
  • Can lead to complex queries with multiple joins, impacting read performance.
  • Results in smaller table sizes, improving write performance.
• Denormalization:
  • Improves read performance by reducing joins.
  • Can lead to data redundancy and increased risk of anomalies.
  • May result in larger table sizes, impacting write performance and storage.
  • Requires careful management for consistency and integrity.

3. How would you partition a large table to improve query performance? Provide an example.

Partitioning a large table divides it into smaller, manageable pieces called partitions, improving query performance by scanning only relevant partitions. Types include range, list, hash, and composite partitioning.

Example of range partitioning:

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2015),
    PARTITION p2 VALUES LESS THAN (2020),
    PARTITION p3 VALUES LESS THAN (2025)
);

In this example, the sales table is partitioned by the year of the sale_date column, allowing efficient execution of queries filtering by sale_date.

4. Explain the differences between clustered and non-clustered indexes and their impact on performance.

Clustered indexes determine the physical order of data in a table, with only one allowed per table. They are beneficial for range queries and large result sets. Non-clustered indexes create a separate object pointing to data rows, allowing multiple per table. They are useful for specific value lookups.

Performance impact:

• Clustered indexes speed up read operations for range queries but can slow down write operations due to maintaining physical order.
• Non-clustered indexes speed up specific value lookups but add overhead to write operations.

5. How would you create and use a materialized view to speed up a complex query?

A materialized view stores the results of a query physically, improving performance for complex, frequently executed queries. Unlike regular views, materialized views do not retrieve data dynamically. They can be refreshed periodically to stay updated.

Example:

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(quantity) AS total_quantity, SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;

To use the materialized view:

SELECT * FROM sales_summary WHERE total_revenue > 10000;

To refresh:

REFRESH MATERIALIZED VIEW sales_summary;

6. Explain the order of operations in SQL query execution and its impact on performance.

In SQL, understanding the order of operations in query execution is essential for optimization. The execution order is:

• FROM/JOIN: Retrieve data and apply JOIN operations.
• WHERE: Filter rows based on conditions.
• GROUP BY: Group filtered rows.
• HAVING: Filter grouped rows.
• SELECT: Specify columns for the result set.
• ORDER BY: Sort the result set.
• LIMIT/OFFSET: Restrict the number of rows returned.

Placing conditions in the WHERE clause rather than HAVING can improve performance, as filtering occurs before grouping. Selecting only necessary columns in the SELECT clause reduces data processing, leading to faster execution.

7. Describe index maintenance strategies and their importance for query performance.

Index maintenance strategies ensure efficient query performance. Over time, indexes can become fragmented or outdated, affecting performance. Key strategies include:

• Rebuilding Indexes: Drop and recreate the index for heavily fragmented ones.
• Reorganizing Indexes: Defragment the leaf level of the index, less resource-intensive than rebuilding.
• Updating Statistics: Keep statistics up-to-date for informed query optimization.
• Monitoring Index Usage: Identify unused indexes to save storage and maintenance overhead.

8. How does network latency affect query performance in distributed databases, and how can it be mitigated?

Network latency affects query performance in distributed databases by delaying data travel between nodes. Mitigation strategies include:

• Data Locality: Store frequently accessed data close to where it’s needed.
• Efficient Query Planning: Minimize data transfer across the network.
• Asynchronous Processing: Overlap computation and communication.
• Network Optimization: Improve network infrastructure.
• Compression: Compress data before transmission to reduce transfer time.

9. Write a query that utilizes advanced indexing techniques such as covering indexes or filtered indexes to optimize performance.

Covering indexes include all columns needed to satisfy a query, allowing it to be answered using the index alone. Filtered indexes include a WHERE clause to index a subset of rows.

Example of a covering index:

CREATE INDEX idx_covering ON Orders (CustomerID, OrderDate, TotalAmount);

Example of a filtered index:

CREATE INDEX idx_filtered ON Orders (OrderDate)
WHERE Status = 'Completed';

10. How would you implement batch processing to improve the performance of inserting multiple rows into a table?

Batch processing improves the performance of inserting multiple rows by grouping operations into a single transaction, reducing overhead. Methods include:

• Single INSERT with multiple VALUES: Reduces round trips to the server.
• Prepared statements with parameterized queries: Efficiently reuse query structure.
• Database-specific bulk insert utilities: Use specialized tools for bulk loading.

Example using a single INSERT statement:

INSERT INTO employees (name, position, salary) VALUES
('John Doe', 'Manager', 60000),
('Jane Smith', 'Developer', 55000),
('Emily Johnson', 'Designer', 50000);

Example using prepared statements in Python:

import psycopg2

conn = psycopg2.connect("dbname=test user=postgres password=secret")
cur = conn.cursor()

data = [
    ('John Doe', 'Manager', 60000),
    ('Jane Smith', 'Developer', 55000),
    ('Emily Johnson', 'Designer', 50000)
]

query = "INSERT INTO employees (name, position, salary) VALUES (%s, %s, %s)"
cur.executemany(query, data)

conn.commit()
cur.close()
conn.close()