Today I am writing to you as I promised to see the solutions to the performance problems involved in using the scalar functions in more complex queries.
The first is to create the scalar function as a table function, that is, instead of returning a single value, a set of values as a normal query.
We create the function:
The scalar function scalar function was used as follows:
To use the table function we have to use a CROSS JOIN as follows:
In this way the number of I/O is much more efficient and CPU usage is much lower.
The other alternative is to create a view. For example:
Now we make a JOIN with the view:
With the view we gain that the CPU usage is a bit lower although the I / O is the same as with the use of CROSS JOIN.
If we put together that it is a little better to use the CPU using a view, this is my preferred way, because it is more understandable for me than using the CROSS JOIN. In addition, the view is always more generic to be able to filter what we want or without any filter, what has the functions that we have to pass the filter beforehand and what allows us to create other functions so that it does not filter anything or to pass more filters or other different.
I have not put any performance test because here I leave an article which explains very well the yields attaching tests with the profiler. It has no waste and is essential if you are interested in the performance tests of everything I have told you.