DEV Community

Sandeep Borhade
Sandeep Borhade

Posted on

4 1

SQL query optimization techniques

Sure! Here are examples for each of the SQL query optimization techniques:

1. Indexing

Create Indexes:

CREATE INDEX idx_employee_name ON Employees(Name);
Enter fullscreen mode Exit fullscreen mode

Use Appropriate Indexes:

SELECT * FROM Employees WHERE Name = 'John';
Enter fullscreen mode Exit fullscreen mode

2. Query Optimization

Avoid SELECT *:

SELECT Name, Age FROM Employees;
Enter fullscreen mode Exit fullscreen mode

Use Joins Efficiently:

SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
Enter fullscreen mode Exit fullscreen mode

Filter Early:

SELECT Name, Age FROM Employees WHERE Age > 30;
Enter fullscreen mode Exit fullscreen mode

3. Query Execution Plan

Analyze Execution Plan:

EXPLAIN SELECT Name, Age FROM Employees WHERE Age > 30;
Enter fullscreen mode Exit fullscreen mode

4. Avoiding Subqueries

Use Joins Instead of Subqueries:

SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
Enter fullscreen mode Exit fullscreen mode

Use EXISTS Instead of IN:

SELECT Name FROM Employees WHERE EXISTS (SELECT 1 FROM Departments WHERE Employees.DepartmentID = Departments.DepartmentID);
Enter fullscreen mode Exit fullscreen mode

5. Caching and Materialized Views

Cache Results:

-- Application-level caching example
Enter fullscreen mode Exit fullscreen mode

Use Materialized Views:

CREATE MATERIALIZED VIEW mv_employee_summary AS
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID;
Enter fullscreen mode Exit fullscreen mode

6. Database Configuration

Optimize Database Settings:

-- Adjust buffer size, cache size, etc. in database configuration
Enter fullscreen mode Exit fullscreen mode

Partitioning:

CREATE TABLE Employees_Partitioned (
    EmployeeID INT,
    Name VARCHAR(100),
    Age INT,
    DepartmentID INT
) PARTITION BY RANGE (Age) (
    PARTITION p0 VALUES LESS THAN (30),
    PARTITION p1 VALUES LESS THAN (60),
    PARTITION p2 VALUES LESS THAN (MAXVALUE)
);
Enter fullscreen mode Exit fullscreen mode

7. Avoiding Redundant Data

Normalize Data:

-- Example of normalizing data into separate tables
Enter fullscreen mode Exit fullscreen mode

Denormalize for Read Performance:

-- Example of denormalizing data for read performance
Enter fullscreen mode Exit fullscreen mode

8. Monitoring and Profiling

Monitor Query Performance:

-- Use database monitoring tools to track query performance
Enter fullscreen mode Exit fullscreen mode

Profile Queries:

-- Profile queries to understand performance characteristics
Enter fullscreen mode Exit fullscreen mode

Sure! Here are examples for the additional SQL query optimization techniques:

9. Use Stored Procedures

Stored Procedures: Use stored procedures for frequently executed queries. They are precompiled and can improve performance by reducing the parsing and execution time.

CREATE PROCEDURE GetEmployeeById
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END
Enter fullscreen mode Exit fullscreen mode

10. Avoid Cursors

Avoid Cursors: Instead of using cursors, use set-based operations.

-- Instead of using a cursor to update rows one by one
-- Use a set-based operation to update all rows at once
UPDATE Employees
SET Salary = Salary * 1.1
WHERE DepartmentID = 1;
Enter fullscreen mode Exit fullscreen mode

11. Optimize Joins

Join Order: Optimize the order of joins to ensure that the most restrictive joins are performed first.

SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.Age > 30;
Enter fullscreen mode Exit fullscreen mode

Join Conditions: Ensure that join conditions are indexed to improve performance.

CREATE INDEX idx_employee_department ON Employees(DepartmentID);
Enter fullscreen mode Exit fullscreen mode

12. Use Appropriate Data Types

Data Types: Use appropriate data types for columns to reduce storage requirements and improve query performance. Avoid using larger data types than necessary.

-- Use VARCHAR(50) instead of VARCHAR(255) if the maximum length is 50
CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(50),
    Age INT,
    DepartmentID INT
);
Enter fullscreen mode Exit fullscreen mode

13. Limit Result Sets

LIMIT/OFFSET: Use LIMIT or OFFSET clauses to limit the number of rows returned by a query, especially when dealing with large datasets.

SELECT Name, Age FROM Employees WHERE Age > 30 LIMIT 10 OFFSET 20;
Enter fullscreen mode Exit fullscreen mode

LIMIT 10: Limits the result set to 10 rows.

OFFSET 20: Skips the first 20 rows and starts returning rows from the 21st row.

14. Regular Maintenance

Maintenance Tasks: Perform regular database maintenance tasks such as updating statistics, rebuilding indexes, and checking for fragmentation to ensure optimal performance.

-- Update statistics
UPDATE STATISTICS Employees;

-- Rebuild indexes
ALTER INDEX ALL ON Employees REBUILD;
Enter fullscreen mode Exit fullscreen mode

Image of Datadog

The Essential Toolkit for Front-end Developers

Take a user-centric approach to front-end monitoring that evolves alongside increasingly complex frameworks and single-page applications.

Get The Kit

Top comments (1)

Collapse
 
stevsharp profile image
Spyros Ponaris ā€¢

Thanks for sharing! Regarding SQL Server, Iā€™d also like to highlight the importance of using Common Table Expressions (CTEs) and Window Functions.

Additionally, leveraging @@ROWCOUNT can eliminate the need for cursors, significantly improving performance. šŸš€

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up