What is SQL performance tuning?

Why performance tune SQL queries?

Queries on large databases can have significant execution times, but too heavy or a load may disrupt service for other users. Several factors must be considered to optimize a database:

• Table Size
• Frequent Queries
• Query Structure

Methods

Below are a few methods that can be used to optimize query runtime.

1. Use Indexes

A straightforward, yet effective, method for query optimization is indexing. Indexes can be thought of as duplicate tables containing records that may be needed often. This way, the whole database won’t need to be searched for these queries. For example, if a system needed to query first and last names and ID numbers of employees based on their age, the following script could be used to create the index:

CREATE INDEX age_queries
ON Employees (last_name, first_name, id, age);

2. Use Inner Join instead of Where

To link data from two or more tables, the Where clause is used. However, Where uses a kind of join that joins each record of each table and then filters it for the result. This means that if there are 100 records in the Employee table and 100 in the Salary table, it will generate a table of 10,000 records and then check for the correct records for this query.

Select Employees.first_name, Employees.last_name, Employees.id
From Employees, Salary
Where Employees.id = Salary.id;

In contrast, if the Inner Join is used, the required records will be produced without anything in the middle.

Select Employees.first_name, Employees.last_name, Employees.id
From Employees Inner Join Salary
On Employees.id = Salary.id;

3. Use Limit for testing

Before running a query on a large database with other potential users, it is advised to use the limit operator to ensure that it actually works. The limit keyword returns a limited number of results from a query, as shown below.

Select Employees.first_name, Employees.last_name, Employees.id
From Employees Inner Join Salary
On Employees.id = Salary.id
Where Salary.amount > 4500
Limit 10;

This will return only ten names and ID numbers of Employees with salaries greater than 4,500.

4. Use Explain

When querying a large database, it might be smart to use the Explain operator before running the query. Explain displays what is called a Query Plan. The query plan shows the order of execution and the approximate time each step might take. Using this information, more time-expensive steps may be better optimized. The following example shows how to use this operator:

Explain
Select Employees.first_name, Employees.last_name, Employees.id
From Employees Inner Join Salary
On Employees.id = Salary.id
Where Salary.amount > 4500;

5. Be Specific with Select queries

When working with Select queries, it is smart to avoid the * operator, which returns all columns for the queried records unless otherwise required. Instead, only the required information should be extracted. For example:

Select *
From Employees;

This operation would generate all columns of all records from the Employees table. This is inefficient if only the names were required. In that case, the following query should be used:

Select first_name, last_name
From Employees;

Overall, to completely optimize database usage, engineers should know how each function works and how the databases that they use are setup. This knowledge will allow them to make technically informed decisions with their queries.