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
|
1 2 3 4 5 6 7 8 |
CREATE TABLE SalesOrders( SalesOrderID INT NOT NULL, SalesOrderDate DATETIME NOT NULL, CONSTRAINT PK_SalesOrders PRIMARY KEY CLUSTERED ( SalesOrderID ASC ) ) |
The simplest SQL Statement would be
|
1 2 3 4 |
SELECT * FROM SalesOrders WHERE SalesOrderDate >= '8/1/2017' AND SalesOrderDate <= '8/2/2017' |
Running this against 1,000,000 records will runs at about 132 Milliseconds
However, we could also run this as
|
1 2 3 |
SELECT * FROM SalesOrders WHERE CAST(SalesOrderDate AS DATE) = '8/1/2017' |
This, running under the same function runs at 132 MilliSeconds
We can also run it using the Convert Function
|
1 2 3 |
SELECT * FROM SalesOrders WHERE CONVERT(DATE, SalesOrderDate) = '7/1/2017' |
This ran at 120 Milliseconds
Finally, lets try something else
|
1 2 3 4 |
SELECT * FROM SalesOrders WHERE SalesOrderDate >= (SELECT CAST(CustomerCreateDate AS DATE) FROM Customers WHERE CustomerID = 1) AND SalesOrderDate <= (SELECT CAST(CustomerCreateDate AS DATE) FROM Customers WHERE CustomerID = 1) |
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!