And avoiding the Performance Problems they Bring
SQL Server developers have to do their work in two different worlds. In one world they use modern, object oriented languages where things like code re-use are best practices.
In this world code re-use is achieved by creating SQL scalar functions that can be called again and again instead of the SQL code being duplicated everywhere as in-line code.
In the other world they have to deal with the 60 year old SQL language where things like functions and any form of code re-use come at a significant cost. Often user defined scalar functions (UDFs) and even built-in scalar functions can cause big performance problems when used in a WHERE clause.
We will look at these problems but first we need to understand a couple of terms involved.
Sargability is a term that means that the optimizer can use an index to resolve a WHERE clause if an appropriate index exists.
Nsargability means that for one reason or another the optimizer cannot use an index to resolve the condition even though an appropriate index exists.
The performance problem comes when a condition in the WHERE clause is not sargable and the optimizer cannot use it to build a good execution plan.
I have found through many years of performance tuning that unsearchable arguments such as these are very often the cause of serious performance issues.
UDFs are by no means the only things that create unsearchable arguments but we will start with their example:
SELECT … FROM …WHERE Year(myDate) = 2017
The SQL optimizer can’t use an index on myDate even if one exists. It will execute this function for every row of the table.
When you see a table column inside a udf like this it is safe to assume that an index will not be used on this filter condition. However try it the following way and it will use an index if one is available.
WHERE myDate >= ’01-01-2017′ AND myDate < ’01-01-2017′
There are many workarounds like this that can help you avoid nsargable arguments. There are so many that I cannot illustrate them all in a short newsletter like this. However you can find a lot of these workarounds by Googling ‘SQL Sargability’
Other Unsearchable Arguments.
There are other conditions beyond UDFs that lead to an nsargable argument. For another example:
WHERE FirstName LIKE ‘%Ernie%’
A wild card in the first position of a string will always require a table scan.
This is often the case in search pages in your application. I have studied this problem and found that almost all users type in the first characters of the string they are searching. It may be that you can remove the leading wild card and use an index to find the string you are looking for.
Implicit or explicit type conversion can also force full table scans. A common example is when a varchar() string is compared to an nvarchar() string in a variable. That will kick off a conversion of the column data to match the variable datatype with an accompanying table scan.
Operators to Avoid if Possible
There are some operators that are technically sargable: <>, IN, OR, NOT IN, NOT LIKE. But they seldom improve performance. Some operators can tell you what something is but others have a hard time telling you what something is not.