7 SQL Query Performance Tuning Tips

One of the best parts of SQL language is that it is easy to learn and follow the commands, all thanks to their simple syntax.

But here is the catch: not all database functions are efficient. Two queries might look similar but vary in terms of the computation time, and this is what makes all the difference. This is why fine tuning SQL queries is essential.

If you are an organization that uses live production database for reporting purposes and to extract up-to-date data, it is even important to optimize SQL queries to avoid putting an unnecessary burden on the production database’s resources.

Ways to Fine Tune Your SQL Queries

Have a clear set of business requirements before you begin

One of the best ways to optimize SQL queries is by doing the right things from the square one. Thus, before you begin, ensure that you have tick marked the following boxes:

• Have you recognized the relevant stakeholders?

It is essential to involve all the relevant individuals and teams while developing the query. Additionally, it is even important to involve the DBA team while querying production databases.

• Have you identified your requirements?

The best practice to ensure that all your requirements are met is by answering 5 sets of questions – Who? Why? What? When? Where?

• Are the identified requirements specific and to the point?

Production database plays a crucial role. Taxing the database with ambiguous requirements is way too risky. Thus, before running a query, ensure that all the requirements are specific, and discussed with the appropriate stakeholders

Master the art of creating indexes properly

Performance tuning in SQL can be done by indexing properly which translates to quicker access to the database during critical times. This is one area where most database novices fall short. They either try to index everything or index nothing, and neither of these approaches works in their favor.

This is because when you do not do any indexing at all, your queries will run slow and put an unnecessary load on the database. On the other hand, if you index everything, your insert triggers won’t work as expected, thus making your database inefficient. The key here is to strike the right balance.

Avoid using SELECT*

SELECT* (read as select all) is often used as a shorthand to query all the data from a table. Although this method works fine for smaller tables, it burdens the database resources unnecessarily when a query is fired on a table with many fields and rows.

The best way here is to define the fields in the SELECT statement to instruct the database to query only the required data to meet the end-goals.

Let’s understand this better with the help of an example:

Here is an inefficient way as this query will fetch all the data stored data in the Users table irrespective of your needs.

SELECT*

FROM Users

This is the more efficient way of querying as it pulls only the required information and prevents your database from getting burdened.

SELECT LastName, Address, Contact

FROM Users

Use temporary tables wisely

Although temporary tables are great to use, they do increase the complexity of a query exponentially. It is highly recommended to avoid using temp tables if your code can be written simply.

However, if you need to deal with a stored procedure which cannot be handled with a single query, using temp tables as intermediaries can put an end to your woes.

Avoid using COUNT()

One of the common ways through which developers check if a certain record exists is by using COUNT() instead of EXISTS(). COUNT() is inefficient because it scans the entire table and counts all the queries that satisfy your condition. On the other hand, EXISTS() is more efficient as it exits the loop as soon as it spots the desired result. This contributes to better functioning, and makes ways for a neater code.

Avoid using wildcard characters at the beginning of LIKE pattern

In order to fine tune your SQL queries, you must avoid using LIKE pattern in the following manner:

SELECT* FROM Customers WHERE address LIKE ‘%bar%’;

Here, the database will not be able to use a suitable index if it exists because of % wildcard. The system starts by performing a full table scan and this takes a toll on its speed. Thus, the better way to write this query is:

SELECT* FROM Customers WHERE address LIKE ‘bar%’;

Avoid using SELECT DISTINCT

Although you can easily eliminate duplicates from a query by using SELECT DISTINCT, this function consumes an appreciable amount of processing power. Further, this query works by grouping all fields in the query to present distinct results. This, in turn, makes it highly inaccurate.

The better way to avoid any duplicate records in your query is by adding more fields. This way, there will be no grouping needed, and the fetched records will be accurate.

For example, here is an inefficient way of doing this:

SELECT DISTINCT FirstName, LastName, State

FROM Users

And here is the efficient way of doing this:

SELECT FirstName, LastName, Contact, Address, State, Zip

FROM Users

Bonus tip: Keep some queries for the off-peak times

To ensure that your production database remains safe and sound, it is highly recommended to schedule certain queries to off-peak times, ideally when the number of concurrent users is lowest. Thus, the middle of the night, 3-5 a.m. is the best time to run queries like:

• Looping statements
• Running SELECT* on large tables with over 1 million records
• Nested subqueries
• Wildcard searches
• CROSS JOINs
• SELECT DISTINCT statements

The Wrap Up

Performance tuning in SQL is important to keep your database healthy, but it is not the easiest tasks to accomplish. The performance of your SQL queries is dependent on a range of factors like your database model, the kind of information that you need to fetch and so on.

It is highly recommended to avoid any awkward situations by keeping a track of all the queries that are soon going to be fired, and providing the best solutions. As a DBA, you might also equip developers with a data-driven dashboard such that they don’t have to fire queries now and then to fetch the essential information. Here is an amazing article on how you can create a SQL dashboard that pulls data directly from the database.

What are your views on this? How do you fine tune your SQL queries? Let us know.