SQL Database Tuning

SQL Database Tuning is the process of improving database performance by optimizing queries, indexing, database design and system resources to ensure faster query execution and efficient data management.

• Reduces data redundancy through normalization
• Improves overall database and system performance

Database Tuning Techniques

Database tuning techniques improve database performance by optimizing queries, indexes, and resource usage to enable faster and more efficient data access.

1. Database Normalization

Normalization removes duplicate data by splitting large tables into smaller, related tables. This improves data consistency, reduces redundancy and makes queries faster. We will normalize the CUSTOMERS table step by step.

Step 1: Denormalized CUSTOMERS Table

Step 2: Normalization (First Normal Form)

To eliminate redundancy, the data is split into two related tables: the Customers table and the Orders table.

Customers Table: stores unique customer details such as CustomerID, Name and City, ensuring that each customer appears only once.

Orders Table: on the other hand, stores information about orders and includes a reference to the corresponding customer through the CustomerID column.

This structure not only removes duplicate data but also establishes a relationship between customers and their orders, making the database more efficient and easier to manage.

2. Proper Indexing

Indexes are database structures that help quickly locate data in a table. They reduce query execution time and improve data retrieval, especially for large databases.

Example: Create an index on the NAME column in a CUSTOMERS table:

CREATE INDEX idx_name ON CUSTOMERS(NAME);

Querying indexed columns:

SELECT * FROM CUSTOMERS WHERE NAME = 'Alice';

• idx_name helps find NAME = 'Alice' quickly
• Avoids scanning the entire table

3. Avoid Improper Queries

Writing efficient SQL queries helps improve database performance by reducing execution time and resource usage. Below are some common query optimization techniques.

1. Use specific columns in SELECT statements: Instead of retrieving all columns using SELECT *, specify only the columns you need. Retrieving unnecessary columns increases data transfer and processing time.

Efficient Query:

SELECT ID, NAME FROM CUSTOMERS;

Avoid

SELECT * FROM CUSTOMERS;

• Selecting only ID and NAME reduces data and improves query speed

2. Use wildcards only with indexed columns: Wildcards are useful for searching patterns, but they should be used on indexed columns to ensure quick lookups.

Efficient Query:

SELECT NAME FROM CUSTOMERS WHERE NAME LIKE 'A%';

• 'A%' finds names starting with A and an index on NAME makes the search faster.

3. Use Explicit JOINs Instead of Implicit JOINs: Explicit JOINs improve query readability and maintainability in complex queries.

Efficient Query:

SELECT c.NAME, o.ORDER_ID
FROM CUSTOMERS c
JOIN ORDERS o ON c.CustomerID = o.CustomerID;

Avoid (Implicit Join):

SELECT c.NAME, o.ORDER_ID
FROM CUSTOMERS c, ORDERS o
WHERE c.CustomerID = o.CustomerID;

• Explicit JOIN makes queries easier to understand and manage.

4. Avoid Using SELECT DISTINCT: DISTINCT removes duplicate rows from a query result.

Inefficient Query (Using DISTINCT):

SELECT DISTINCT NAME FROM CUSTOMERS;

Optimized Query (Using GROUP BY):

SELECT NAME FROM CUSTOMERS GROUP BY NAME;

• DISTINCT removes duplicate rows and may increase processing time on large datasets.
• GROUP BY can be used to achieve the same result.

5. Avoid Multiple OR Conditions: In some cases, replacing multiple OR conditions with UNION can improve performance because each query can use indexes separately.

Inefficient Query (Using OR):

SELECT * FROM CUSTOMERS WHERE AGE > 30 OR SALARY > 5000;

Optimized Query (Using UNION):

SELECT * FROM CUSTOMERS WHERE AGE > 30
UNION
SELECT * FROM CUSTOMERS WHERE SALARY > 5000;

• OR checks both conditions for every row, which can slow the query.
• UNION runs each condition separately and then combines the results.

Use WHERE Instead of HAVING: The WHERE clause is more efficient than HAVING as it filters data before grouping.

Inefficient Query (Using HAVING):

SELECT DEPARTMENT, AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT
HAVING AVG(SALARY) > 5000;

Optimized Query (Using WHERE):

SELECT DEPARTMENT, AVG(SALARY)
FROM EMPLOYEES
WHERE SALARY > 5000
GROUP BY DEPARTMENT;