Hey guys!
Goodnight!

Today I came across a critical performance problem in a production environment, where a certain query (which can be executed several times per second) was presenting a slow problem (it took between 21 and 30 seconds per execution) which was already old and known, but today was the day to resolve it once and for all.

For many DBA's (including myself) and database modeling enthusiasts, this type of problem should never occur, but unfortunately in practice this is not what happens. Analyzing the query, which by the way was quite large, with several CASES, LEFT JOIN’S, OR’s, use of functions, etc. I could see that there were many cases of implicit conversion and when analyzing the actual execution plan, the reason for the slowness was very clear:

SQL Server - JOIN Predicate Columns Different DataTypes 2
SQL Server - JOIN Predicate Columns Different DataTypes 2

Analyzing the disk reads, we can observe that the reads in a specific table are very high, especially if we take into account that the query uses in the WHERE clause an equality term in the main table of the query, informing a code that is the primary key and is in the clustered index, that is, it should be an extremely fast and optimized query, processing few records.

SQL Server - JOIN Predicate Columns Different DataTypes 3
SQL Server - JOIN Predicate Columns Different DataTypes 3

Notice that there is a warning in the select. And by positioning the mouse over it, we can view this information:

SQL Server - Differente Datatypes Performance Problems 2
SQL Server - Different Datatypes Performance Problems 2

Well, we have the implicit conversion problem occurring. This occurs when two columns of different data types are compared and then the database needs to convert this manually during the query.

Analyzing the JOINS of the query, I could see that the columns involved in the comparison were of different types. This means that all records involved in the JOIN have to be converted to the same type, and then check whether they should be restricted by the JOIN clauses or not. If the volume of records is very high, this can considerably increase processing time and disk reads (as demonstrated in the example)

A simple ALTER TABLE command on the column that was performing the JOIN of the table that had many readings to match the data types solved the problem:

SQL Server - JOIN Predicate Columns Different DataTypes 1
SQL Server - JOIN Predicate Columns Different DataTypes 1

SQL Server - JOIN Predicate Columns Different DataTypes 4
SQL Server - JOIN Predicate Columns Different DataTypes 4

Another solution would be to create an intermediate table containing part of the query, and converting the column to the correct type. With this intermediate table, the JOIN would be carried out with the rest of the query, and now, with the same type of data, there was no longer the problem of implicit conversion and the query would be executed in a more optimized way.

That's it, folks!
A hug and see you in the next post!