SQL Interview High-Frequency Questions Optimization

Common Interview Questions

SQL interviews often test candidates on their ability to write efficient queries and optimize performance. Here are some frequently asked questions and their optimization strategies:

1. SELECT * vs. SELECT columns

Optimization: Use SELECT columns instead of SELECT * to reduce data transfer and improve performance.

Example:

SELECT id, name FROM users;  
-- Instead of:  
SELECT * FROM users;

2. JOINs vs. Subqueries

Optimization: Prefer joins over subqueries for better performance and clarity.

Example:

SELECT a.id, a.name  
FROM users a  
JOIN orders b ON a.id = b.user_id;  
-- Instead of:  
SELECT a.id, a.name  
FROM users a  
WHERE a.id IN (SELECT b.user_id FROM orders b);

3. Indexing Strategies

Optimization: Create indexes on columns used in WHERE, JOIN, and ORDER BY clauses.

Example:

CREATE INDEX idx_users_email ON users(email);

4. Query Execution Plans

Optimization: Analyze execution plans using EXPLAIN PLAN to identify bottlenecks.

Example:

EXPLAIN PLAN FOR SELECT * FROM users WHERE email = 'example@example.com';  
SELECT * FROM TABLE(EXPLAIN_PLAN) ORDER BY cost;

**5. Avoiding SELECT ***

Optimization: Explicitly specify columns to reduce I/O and memory usage.

Example:

SELECT id, name, created_at FROM users WHERE id = 1;

Optimization Techniques

Optimizing SQL queries requires a combination of understanding database internals and practical experience. Key techniques include:

1. Indexing

Best Practices: Use covering indexes to avoid table scans.

Example:

CREATE INDEX idx_orders_status ON orders(status);

2. Query Rewriting

Best Practices: Simplify complex queries and avoid unnecessary joins.

Example:

SELECT u.id, u.name  
FROM users u  
WHERE u.status = 1 AND u.created_at > CURRENT_DATE - INTERVAL '1 month';

3. Avoiding Full Table Scans

Best Practices: Ensure that the query uses indexed columns in WHERE and ORDER BY.

Example:

SELECT id, name FROM users WHERE status = 1 AND created_at > '2023-01-01';

4. Using Caching

Best Practices: Cache frequently accessed data to reduce database load.

Example:

-- Use a materialized view for cached results  
CREATE MATERIALIZED VIEW cached_users AS SELECT * FROM users WHERE status = 1;

5. Partitioning

Best Practices: Partition large tables by date or range to improve query performance.

Example:

CREATE TABLE users_partitioned  
PARTITION BY RANGE (created_at) (  
  PARTITION p2023 VALUES LESS THAN ('2023-04-01'),  
  PARTITION p2024 VALUES LESS THAN ('2024-01-01')  
);

Advanced Optimization Strategies

For more complex scenarios, advanced optimization techniques are necessary:

1. Materialized Views

Best Practices: Use materialized views to store precomputed results for faster queries.

Example:

CREATE MATERIALIZED VIEW cached_orders AS  
SELECT * FROM orders WHERE status = 1;

2. Query Execution Plans

Best Practices: Analyze execution plans to identify inefficient operations.

Example:

EXPLAIN PLAN FOR SELECT * FROM users WHERE email = 'example@example.com';  
SELECT * FROM TABLE(EXPLAIN_PLAN) ORDER BY cost;

3. Indexing Best Practices

Best Practices: Use composite indexes for multi-column queries.

Example:

CREATE INDEX idx_users_email_date ON users(email, created_at);

4. Connection Pooling

Best Practices: Use connection pooling to reduce overhead and improve throughput.

Example:

-- In PostgreSQL:  
CONNECTION_POOL_SIZE = 100;

5. Avoiding Deadlocks

Best Practices: Design queries to avoid locking tables or rows unnecessarily.

Example:

-- Avoid:  
BEGIN;  
UPDATE users SET status = 2 WHERE id = 1;  
COMMIT;  
-- Prefer:  
BEGIN;  
UPDATE users SET status = 2 WHERE id = 1;  
SELECT * FROM users WHERE id = 1;
COMMIT;

Conclusion

Optimizing SQL queries is critical for performance and scalability in real-world applications. By focusing on efficient indexing, query rewriting, and execution plan analysis, developers can reduce latency and improve resource utilization. Advanced techniques like materialized views and partitioning further enhance performance for large datasets. Understanding these strategies enables interview candidates to write optimized, efficient SQL code that meets high-performance requirements.