Posted on Leave a comment

Why Make a SQLChallenge on Tuning Functions? Why Not Say, ‘Stop Using Functions’?

I’ve published a new SQLChallenge for subscribers: Fix My Function.

This 200-Level challenge features a terrible, awful, very-bad function, which you are dared to tune.

The rules of the challenge include that you can rewrite the function and rewrite the query, but you still need to keep the function logic in a function.

What’s wrong with user defined functions?

I’m going to quote from a recent Microsoft paper of note a few times in this post: Froid: Optimization of Imperative Programs in a Relational Database. The paper is by Karthik Ramachandra, Kwanghyun Park, K. Venkatesh Emani, Alan Halverson, Cesar Galindo-Legaria and Conor Cunningham.

The introduction to the paper explains why user defined functions are both loved and hated.

UDFs are loved because they have loads of advantages

The paper lists these advantages, which are pretty persuasive. Functions can be very powerful because…

(a) They are an elegant way to achieve modularity and code reuse across SQL queries, (b) some computations (such as complex business rules and ML algorithms) are easier to express in imperative form, (c) they allow users to express intent using a mix of simple SQL and imperative code, as opposed to complex SQL queries, thereby improving readability and maintainability.

Expressing and coding business rules clearly, in a way that can be easily reused and maintained: that’s not a small benefit! That’s why functions are very common in database code, even though….

UDFs are hated because they often have big performance problems

I’m not going to be able to say this better than the paper. It explains:

It is a known fact amongst practitioners that UDFs are “evil” when it comes to performance considerations.

This is an academic paper, so there are citations regarding the evil of functions! That makes me laugh so hard.

If you’re wondering who was cited on the evil of functions, the articles are  by Simon Sabin and Arvind Shyamsundar.

Functions look like they’re going to shed some of their ‘evil’

The paper on Froid goes on to describe very cool new optimization techniques to help keep all those benefits of functions without killing performance. These optimizations solve a lot of the pains.

Pains like: user defined scalar-functions (of the non-CLR variety) prevent parallelism in the queries that call them.

The features described in the paper transform the logic in the function and bring them into the query plan itself, allowing parallelism and other optimizations. If you don’t want to read the whole paper, the abstract is pretty clear about this.

It’s still well worth knowing how to tune functions

There are reasons to still learn your way around tuning a function in SQL Server, and not just wait for v-Next (the version after SQL Server 2017).

  1. We don’t know exactly which functions can be optimized by these new function Froid-ing features
  2. We don’t know what Froid will cost / which editions will get it
  3. When there’s a simpler way to write or tune something, it’s often better!

Those reasons are why I wrote  Fix My Function. (Besides it being a lot of fun)

This SQLChallenge is a 200 level problem, but the videos documenting sample solutions get into the 300-level area by showing how to use estimated and actual plans to understand what functions are doing, and how to use SQL Server’s DMVs to measure function performance in different ways.

If you’ve already subscribed, you already have access to this course: dive right in.

If you’re ready to join, sign up here!

Posted on Leave a comment
Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.