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!

14 comments

  1. I want to express my admiration for your kindness for those who really need guidance on in this field. Your real dedication to passing the solution along appears to be extraordinarily practical and has continuously enabled men and women like me to realize their endeavors. Your entire invaluable advice implies this much to me and additionally to my mates. Thanks a lot; from each one of us.

  2. Thank you for every one of your effort on this blog. My aunt really likes engaging in research and it is simple to grasp why. We know all about the lively form you make effective strategies via your blog and boost response from website visitors about this situation and our own child is without a doubt being taught so much. Enjoy the rest of the year. You have been carrying out a very good job.

  3. I together with my friends happened to be studying the excellent procedures found on the website and then unexpectedly I got a terrible feeling I had not thanked the site owner for them. The boys had been for this reason passionate to read them and have actually been enjoying those things. Many thanks for turning out to be simply thoughtful and then for opting for some wonderful areas millions of individuals are really needing to be informed on. My personal sincere apologies for not expressing appreciation to earlier.

  4. My husband and i felt absolutely thrilled that Michael could deal with his inquiry through your ideas he was given from your site. It is now and again perplexing to just always be giving out techniques which often some other people could have been selling. We really do know we now have the writer to be grateful to because of that. These illustrations you made, the straightforward web site navigation, the relationships you make it possible to instill – it is mostly impressive, and it’s really helping our son in addition to our family reason why this matter is excellent, and that’s rather mandatory. Thanks for everything!

  5. Thank you for your own efforts on this blog. Debby enjoys setting aside time for investigations and it’s really easy to understand why. Most of us hear all regarding the powerful method you produce vital suggestions via your web site and as well as cause participation from some other people on that matter so our favorite girl is certainly becoming educated so much. Enjoy the rest of the new year. Your performing a remarkable job.

  6. I’m writing to let you be aware of of the superb encounter my friend’s child gained checking your webblog. She realized a wide variety of details, most notably what it is like to have an excellent teaching mindset to let other people without difficulty fully understand a variety of multifaceted matters. You really surpassed people’s expected results. Many thanks for providing these interesting, safe, edifying and as well as unique tips about this topic to Ethel.

  7. Thanks so much for giving everyone an extremely wonderful opportunity to check tips from this blog. It is usually so terrific and also packed with amusement for me and my office friends to search your web site minimum thrice in a week to learn the latest stuff you have. Of course, I’m also at all times amazed concerning the gorgeous creative concepts you serve. Selected 3 ideas in this post are ultimately the most beneficial I have ever had.

  8. I would like to show my appreciation for your kind-heartedness supporting men and women who really want help on the study. Your very own commitment to getting the solution along became pretty effective and have constantly allowed guys and women just like me to achieve their goals. Your valuable key points implies a great deal to me and much more to my office workers. Thank you; from all of us.

  9. I actually wanted to post a brief remark in order to express gratitude to you for all the awesome hints you are placing on this site. My time consuming internet investigation has finally been recognized with excellent knowledge to talk about with my neighbours. I would state that that we site visitors are unquestionably blessed to be in a superb network with so many perfect individuals with insightful strategies. I feel pretty lucky to have encountered your entire site and look forward to so many more thrilling moments reading here. Thank you again for everything.

  10. Thank you for all of your effort on this web page. My mother really loves participating in research and it’s really easy to understand why. My partner and i notice all regarding the compelling medium you deliver both useful and interesting steps on your website and in addition improve participation from others about this concept while my child is actually understanding a whole lot. Take pleasure in the remaining portion of the year. You’re the one conducting a brilliant job.

  11. I in addition to my friends were actually checking the great secrets found on your web blog and so quickly I had an awful feeling I had not thanked the site owner for those strategies. Most of the boys had been as a result very interested to read them and already have certainly been enjoying them. Appreciation for genuinely simply kind and then for settling on such fantastic areas millions of individuals are really desperate to know about. Our honest apologies for not expressing appreciation to sooner.

  12. I precisely wished to say thanks once more. I’m not certain the things I would have made to happen without those aspects documented by you on such a concern. It was before an absolute fearsome scenario in my view, nevertheless looking at the very skilled manner you processed it took me to weep over contentment. I will be grateful for this guidance and believe you really know what a great job that you’re providing instructing others via your blog. I am sure you have never got to know all of us.

  13. I’m just writing to let you know what a beneficial encounter my wife’s princess went through reading your blog. She even learned many details, with the inclusion of what it’s like to possess an incredible helping spirit to let other individuals quite simply have an understanding of certain grueling issues. You undoubtedly exceeded our expectations. Thank you for showing those useful, healthy, edifying and in addition cool tips about that topic to Ethel.

  14. I would like to show my thanks to the writer just for bailing me out of this particular scenario. As a result of checking throughout the internet and obtaining techniques that were not helpful, I assumed my entire life was done. Being alive devoid of the strategies to the problems you’ve sorted out all through the site is a serious case, and the ones that would have adversely damaged my career if I had not encountered your blog. Your primary understanding and kindness in taking care of a lot of stuff was useful. I’m not sure what I would’ve done if I hadn’t discovered such a subject like this. It’s possible to now relish my future. Thanks very much for your expert and sensible help. I won’t hesitate to propose your blog to any individual who will need tips on this issue.

Leave a Reply

Your email address will not be published. Required fields are marked *