Function Calls are Evil Incarnate - Help Writing Faster SQL Queries - Part 2 of 3

Hello again and welcome to our blog.

This is part two of our three part series to help you write faster queries.

In part one you learned about the power of indexes, when an index should be used and how to create an index.

Let's get on to part two, shall we?


Help Writing Faster SQL Queries
Part 2 : Function Calls are Evil Incarnate!

The following queries look extremely innocent. However, they are all application killers once the table grow to more than a couple thousand rows:

Select Person_Data_ID, Name_First, Name_Last
From Person_Data
Where Next_Contact_Date > GetDate()

Select Count(*)
From Person_Data
Where Upper(Name_Last) = ‘SMITH’

Select Avg(Collar_Size)
From Person_Data
Where dbo.MyFunc(Person_Data) > dbo.MyOtherFunc(22)

For each of these statements, a table scan will be used to try and fetch data.

A table scan is simply an operation where the database looks at each and every record in the table to see which ones meet it’s criteria in the Where clause.

We would much rather the database use an index and only fetch the 20 records it needs rather than reading 200,000 records in a table scan fto locate the 20 records it needs. However, the use of the functions (GetDate(), Upper(), MyFunc(), MyOtherFunc(), etc) keep it from doing that.

Why do functions keep the database from using indexes? It’s simply because the database does not know the result of the function.

Each time GetDate() is called, a different answer could come back. Each time MyFunc() is called it could come back with a different result since the function could potentially be based on the input value, the time of day, or data that is being modified elsewhere in the database. The only way the database can know for certain the answer to the function call is to make it and see what the answer is. Since it doesn’t know ahead of time what the answer is, it cannot use the index to go to the corresponding entry.

So, whenever possible, avoid the use of functions in your Where clauses. A great solution to these problems are (in order):

Select Person_Data_ID, Name_First, Name_Last
From Person_Data
Where Next_Contact_Date > GetDate()

Becomes

Declare @DT DateTime
Set @DT = GetDate()
Select Person_Data_ID, Name_First, Name_Last
From Person_Data
Where Next_Contact_Date > @DT

Voila, the database optimizer gets the value of GetDate() ahead of time. Since the value of @DT will not change while the query is running, it can use an appropriate index.

Select Count(*)
From Person_Data
Where Upper(Name_Last) = ‘SMITH’

Becomes

Select Count(*)
From Person_Data
Where Name_Last = ‘SMITH’

“Whoa?” I hear you cry. All you did was take the Upper() function out. What about case sensitivity?!? Actually, I’m cheating a bit here. With Microsoft SQL Server, the default index collations are case insensitive. In plain terms, that means the searches are case insensitive by default. With Oracle and other database engines, you can choose to make your indexes case insensitive, so why not? For a simple name search, it’s a good idea. And, it avoids the dreaded Table Scan.

The third example is a bit tougher. Essentially, if your function is extremely complex, you may not have a choice but to use it and let the user know to expect poor performance (a please wait screen goes a long way here, guys). However, if the function is merely a simple multiplication (i.e. Unit_Price * Qty) then simply do the math in the statement and avoid the function.

Please note: Many developers have it beaten into them to make reusable code so changes can be made in one central place. The fact of the matter here is that a SQL statement is not the same as a C program!! You should adjust accordingly. One of the adjustments is to give up reusable code in this context in favor of better performance.

We hope you have enjoyed the information presented in Part 2 of our series. Were you paying attention? Let's do a quick test and find out?

Is the following statement true or false?

You should always strive to use function calls in your Where clause to maximize Code Reuse and eliminate redundant code from your database?

We really hope you said the answer to the above question is false!

Part three will be ready in a day or so. We recommend that you bookmark this blog now so you can easily come back tomorrow and learn more great SQL Server tuning practices.

The SQL Coach Team