Do you have a slow SQL query?
If so, you’re in the right place.
In this article, you’ll learn a range of reasons why a SQL query may be slow and how to fix them.
There may be a few things you can change to get the query performing well.
Let’s take a look.
Check the Database Load
One of the first things to do is to check how busy the database is. If the database is doing a lot of work at the moment, or under a high load, then all queries including yours will run slowly.
To check this, here are some queries you can start with (which is much easier than asking all of the developers).
Oracle:
SELECT *
FROM v$sql;
SELECT *
FROM v$session;
SQL Server:
SELECT TOP 20
qs.sql_handle,
qs.execution_count,
qs.total_worker_time AS Total_CPU,
total_CPU_inSeconds = --Converted from microseconds
qs.total_worker_time/1000000,
average_CPU_inSeconds = --Converted from microseconds
(qs.total_worker_time/1000000) / qs.execution_count,
qs.total_elapsed_time,
total_elapsed_time_inSeconds = --Converted from microseconds
qs.total_elapsed_time/1000000,
st.text,
qp.query_plan
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;This was used from this page on JohnSansom.com. More information on the Cross Apply feature can be found here.
MySQL:
SHOW PROCESSLIST;
PostgreSQL:
SELECT *
FROM pg_stat_activity;
Is Your Table Locked?
Sometimes the query can take a long time because it’s trying to access a table that’s locked from another process.
This can happen if the table is being updated. Sometimes it can even happen if it’s being selected!
Use one of the queries above, or something similar, to find queries that are currently running on the database. Check that they are not accessing the table you’re working with.
Consider Staging Tables
If your query is complex, it could be rewritten to use a staging table instead of a single large query.
This means that part of the results you need will be stored in a table, which you can then query in your main query.
This strategy is useful if you have a set of data that’s used or accessed more than once. Instead of calculating it each time, you can store the calculated results in a table and query from that.
You can use a Create Table As Select, which creates a new table based on the results of a Select query. Or, if the table already exists, you can use Insert Into Select, to add data to the table.
Avoid Multiple Views
Views are objects in the database that let you run a predefined query. They can help you simplify your queries and improve security.
However, if views are run on other views, then you can end up having multiple queries run on top of each other. You’ll query a view, which queries another view, which queries a table. This adds extra complexity and can slow down your query.
Consider only accessing one view for your query. If you have multiple views for a query, where a view is querying another view, consider creating a new specialised view for it.
Create Indexes
Indexes are a simple and effective way to improve the performance of your query.
An index is a database object that stores the references of rows in a table based on a specific column. It’s like an index in the back of a book.
If you are running a query and it’s slow, it could benefit from indexes.
Indexes are often the most useful when created on columns as part of the Join clause and the Where clause. So, check which columns are used there, check if they have indexes, and create them if they don’t.
There’s more to learn on indexes, including other scenarios where they work, which I’ve written about here.
Avoid Select *
SELECT * is a way to show all columns in a table. It’s faster than typing out all of the columns.
It can be tempting to select all of the columns and just let your application or report use what it needs.
However, selecting more columns than what you need can slow down your query, as the database needs to do extra work to retrieve the columns.
To avoid this, only select the columns you need.
For example, instead of this:
SELECT *
FROM employee;Try this:
SELECT id, first_name, last_name, salary
FROM employee;You’ll get only the columns you need, and nothing else. Your query may perform better as well.
Avoid Functions Unless Necessary
Functions can be useful in SQL. They can help you transform the data into what you need.
However, sometimes they can slow down your query. They can take time to transform the data, which can really add up if you’re selecting a lot of data.
Using functions in a Where clause or Join clause can also slow down your query. For example, if you filter on an uppercase word, then the value in every row needs to be converted to uppercase to do the conversion:
SELECT id, last_name, salary
FROM employee
WHERE UPPER(first_name) = 'JOHN';Instead, try not to use functions in the WHERE clause, and consider if you need to use them in other places at all:
SELECT id, last_name, salary
FROM employee
WHERE first_name = 'John';
Avoid NOT IN
There are two ways to exclude a set of data from a query: NOT IN and NOT EXISTS.
In most cases, NOT EXISTS performs better than NOT IN. This is because with NOT IN, every value needs to be checked. With NOT EXISTS, you would write a query that returns a value, and is often faster.
So, if you have any NOT IN keywords used in your query, try testing it with NOT EXISTS.
Consider CTE Performance
CTEs, or Common Table Expressions, are a great feature. They can really help improve your SQL queries by making them easier to read and understand.
Sometimes they can improve the performance of your query, as the CTE is run only once and used multiple times in your main query.
Other times they can slow down the query, as the database doesn’t optimise them correctly.
So, if you’re having performance issues, consider writing a CTE for your query and testing the performance. Or, if you have a CTE already, consider updating the query to not use one.
Different databases treat this differently, so try them both and see what works better.
Only Use Wildcards Where Needed
Wildcards can be helpful to find partially matching data.
However, they are expensive operations.
Searching all values in a column to partially match a value can take a long time.
SELECT id, first_name, last_name
FROM employee
WHERE last_name LIKE 'B%';If you’re using a wildcard match, then consider if it’s really what you need. Can it be substituted with a different WHERE clause? Can you look for the exact values and use an IN or EXISTS clause instead?
One scenario where I’ve seen a wildcard search needed is for user search queries. However, there are other ways that a user search can be done, using newer specialised technology.
Conclusion
These suggestions for improving a slow SQL query can help in most situations. Knowing what your query is trying to do and avoiding some of the common problems that slow down SQL queries is a great way to improve the performance.
Great Article with excellent and meaningful examples.