10 Ways to Tweak Slow SQL Queries


You’ve gathered the requirements, checked out the tables, and you’re ready to push execute on the queries.

The query runs.

and runs.

and runs…

Before you rage quit or call in your DBA, check these things aren’t slowing things down. A few tweaks could be all you need to get things moving again.

 


1 – Timing is everything
2 – Locked out
3 – You don’t need ALL the things
4 – Uppers and Lowers
5 – Not, NOT IN
6 – To CTE or not to CTE?
7 – Wild, wild, wildcards
8 – Try a table
9 – Views on views on views
10 – Indexes


This is written with PostgreSQL in mind but if you’re using something else, these may still help, give them a try.


1 – Timing is everything

Before we go any further. How busy is the database? Are you running a complex query at a peak time when there are multiple users competing for memory?

Strategy:

You can do this by asking the database … not by shoulder tapping everyone in the company.

This may have been locked down by your friendly DBA so results may vary

 

 


2 – Locked out

Is the table being updated? If you’ve crashed into a table that is being updated by an ETL process you may be blocking the update and won’t get far in running your query.

Strategy:

Have a chat with the team doing ETL and make sure you know when the update window is.


3 – You don’t need ALL the things

If you’ve got the all clear, then we can start pulling your query apart to see if there are any tweaks that could be made before using the query planner or the DBA.

Starting at the top … Do you really need to SELECT * ?

Strategy:


4 – Uppers and Lowers

PostgreSQL is case sensitive which can take some getting used to if you’ve come from SQL Server.

Strategy:


5 – Not NOT IN

Try to avoid the use of ‘IN’ or ‘NOT IN’. By doing this you are performing a full table scan as the query engine looks through every row to check if the condition is met.

Strategy:


6 – To CTE or not to CTE

While CTEs are arguably easier to read than subqueries, in PostgreSQL they are an ‘optimisation fence’, preventing the query optimiser from rewriting queries by moving constraints into or out of the CTE.

Strategy:


7 – Wild, wild, wildcards

Using wildcards at the start and end of a LIKE will slow queries down. And potentially give you more results than you intended.

Strategy:


8 – Try a table

Running several nested queries as a Function is costly and could be quicker if you were to write into a table.

Strategy:


9 – Views on views on views

Views are queries that run when you access the view. If you are calling multiple views, or worse, views on views on views you are asking the query engine to run multiple queries to return your columns.

Strategy:


10 – Indexes

Indexes speed up your query by ordering the data so the database engine knows where to look, or giving it a lookup table so it can use that to know where to look. The type of index you use determines which way the index works.

Strategy:


Hope these tips help with your query tweaking. Every database is different so as always when it comes to the best strategy for your query, it depends.

What strategies do you use to tweak your slow SQL queries before you shoulder tap your DBA and use and interpret the Query Plan?

 


Photo by icon0.com on Pexels

 

# # #

April 6, 2019

Bitnami