MS SQL Functions within the WHERE Clause, the knife in the back to SQL!

While speaking with an Old School programmer, we were discussing how to do an SQL search for a date value from a Date/Time value, and I mentioned that SQL Functions within the WHERE Clause of an SQL Statement will bring the SQL Server to a screeching crawl as it scans every single record, bypassing every single index and shortcut it already knows, inserting the values into the function to see if it will satisfy the condition. The other programmer had never heard of that, so I figured, lets dig into it a bit…

I wrote a VBScript implementation to attempt to have other provided validation of this,

Let’s take a simple task, find all of the Sales Orders for a specific Date

The simplest SQL Statement would be

Running this against 1,000,000 records will runs at about 132 Milliseconds

However, we could also run this as

This, running under the same function runs at 132 MilliSeconds

We can also run it using the Convert Function

This ran at 120 Milliseconds

Finally, lets try something else

This ran at about 0 Milliseconds (REALLY?!?!?!). [Just in case anyone pops up and posts about it being cached and already knowing what to search for, after every run, I use FREEPROCCACHE, FREESYSTEMCACHE, sp_recompile N’SalesOrders’ and DROPCLEANBUFFERS to avoid all of that].

These results were in no way what my original expectations were. I decided to do a bit of tweaking to see what the heck was going on. I suspected that the issue could be that my table was so small or perhaps my table is so small or that the Convert/Cast were built within the actual SQL Statements, and I might have more luck trying to build something around strings (like LEFT or SUBSTRING)

First things first, I added an index upon the Date Field, this did have some change, but not exactly what I was expectiong, it sped up the searches most definitely,

  • WHERE SalesOrderDate >= ‘7/1/2017’  AND SalesOrderDate <= ‘7/2/2017’ = 52 MilliSeconds
  • WHERE CAST(SalesOrderDate AS DATE) = ‘7/1/2017’ = 48 MilliSeconds
  • WHERE CONVERT(DATE, SalesOrderDate) = ‘7/1/2017’ = 48 MilliSeconds
  • WHERE SalesOrderDate >= (SELECT CAST(CustomerCreateDate AS DATE) FROM Customers WHERE CustomerID = 1) AND SalesOrderDate <= (SELECT CAST(CustomerCreateDate AS DATE) FROM Customers WHERE CustomerID = 1) = 0 MilliSeconds

What about if we were to handle as for converting it the VarChar (like they did in the old days before DATE was an option)? AH-HA!…

  • WHERE SalesOrderDate >= ‘7/1/2017’  AND SalesOrderDate <= ‘7/2/2017’ = 60 MilliSeconds
  • WHERE CONVERT(VARCHAR(20), SalesOrderDate, 101) = ’07/01/2017′ = 480 MilliSeconds

Some other key points, using the new conversion, I had to ensure that it matched perfectly (yep, it is a VARCHAR now), and I first ran it as 7/1/2017 and received 0 records…  I am also going to go under the assumption that converting one data type to another one that is very similar is something very easy for and has been improved upon recently… but for now, I am going to take this as a fresh point.

Other Blogs pertaining to this topic, for your review… Avoid SQL Server functions in the WHERE clause for Performance and WHERE Clause Functions: A Word of Caution…

Until next time, Happy Coding and may you be blessed!