[ SQL performance Killers ] - Functions to column data
An important part of my daily work is to help developers, database administrators, devops, sysadmins and project managers to identify (using Nazar.io) and fix bad SQL code. And the idea with the [SQL performance Killers] series is to share some practical sql tuning examples.
For the first example I’ve chosen a very common “SQL performance killer” which is applying functions to column data. When a function is applied to column data, most of the times it prevents an index (if it exists) to be used by the database.
The recommendation in this case is to try to rewrite the SQL query and apply functions to the parameters of the query instead of applying function to the column data. When it’s not possible to rewrite the query an option is to create a function-based index if the database you are currently using has this feature.
The query below applies TO_CHAR and DATE_PART functions to the column “senha.dataemissao” which prevents the index on the column to be used. Consequently the database (postgresql) does a Sequential Table Scan on table senha, which has 12291867 rows, causing the query to take almost 8 seconds to be executed.
By rewriting the query and applying the data “transformation” on the parameters, instead of applying functions to column data, the database is able to use the “ix_senha_dataemissao” index to access the table “senha”. Using the index, the execution time of the query dropped from almost 8 seconds to only 382 milliseconds what is less than 5% of the original query time. Another important point to notice is that the cost of the query dropped from 671444.38 to 10.21.
“It is not lack of hardware,
It is not network traffic,
It is not slow front ends,
the main performance problem in the huge majority of database applications is bad SQL code.”
Joe Celko
