15 February 2018

2 Comments

15 February 2018

2 Comments

Misuse of the scalar user-defined function as a constant (PE017)

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Do not use a scalar user-defined function (UDF) in a JOIN condition, WHERE search condition, or in a SELECT list, unless the function is schema-bound. Scalar UDFs are often used without a parameter to return a global constant: Unless you need to use these as constants in computed columns or constraints, it is generally safer and more convenient to store the value in an inline table-valued function, or to use a view.

The incorrect use of a scalar UDF as a global database constant is a major performance problem and must be winkled out of any production code. The problem arises because SQL Server doesn’t trust non-schema verified scalar functions as being precise and deterministic, and so chooses the safest, though slowest, option when executing them. It’s a slightly insidious problem because it doesn’t really show its full significance in the execution plan, though an Extended Events session will reveal what is really going on. SQL Prompt implements static code analysis rule, PE017, designed precisely to help you detect and rectify this problem.

PE017 – Incorrect usage of const UDF

Developers tend to expect to be able to set global values in a database to provide constants such as the value of Pi, or variables such as the tax rate, language, file URNs or URLs. User-defined scalar functions return a single value, and so seem to provide the ideal way of doing so. This is fine for functions that are executed infrequently, and process relatively small data sets, but in other cases it can cause dramatic query performance problems. There is an overhead in calling any SQL Server function that has a BEGIN…END block because, unless we allow SQL Server to verify its output, by creating the function using schema binding, it will re-execute the function for every row, before data can be filtered, even though it is obvious to you that it will return the same value every time. Unless you are confident with schema binding, and its consequences when making database changes, it is better to use either transfer the value to a variable, or use a module such as a view or inline Table-value function.

Tackling the Problem

If SQL Prompt detects that your code flouts PE017, what should you do?

We’ll set up all of the possible options, run some performance tests, and make some recommendations.

Schema-qualifying UDFs

A scalar function can be used correctly by adding schema binding to ensure that it is system-verified. Listing 1 creates two versions of the same, simple Wordcount function, first without and then with schema binding, both of which simple return a constant. In each case, we check the IsDeterministic, IsPrecise and IsSystemVerified property values for each object.

Finally, it creates a third version that simply returns its parameter value, just to check to see if this is a factor in SQL Server’s verification process.

Listing 1

If you run Listing 1, you’ll see that only the second version of the function, WordCountSchemaBound, returns true for the three properties. We’ll see a little later just how much this affects the performance of any queries that call these functions.

Although schema-binding has many advantages, it means, in this case, that you’ll be explicitly prevented from treating the constant as a variable, which is no bad thing. If you alter the ‘constant’ function, which is being you’ve used in constraints or computed columns within tables, it will prove to be complicated. Also, if you try to change the constant when the database is working, the plans that use the function that are being executed will place schema stability locks on the function and this will prevent you changing the value of the constant, as they require schema modification lock.

Alternatives to scalar UDFs

Listing 2 shows a couple of alternatives to scalar UDFs to hold database-wide values, in cases where you can’t or don’t want to schema-bind them; first a view, and then a table-valued function.

Listing 2

The objects referred to in the view definition can’t be altered in a way that would make the view definition illegal, or force SQL Server to recreate the index on the view.

I’ve left out the alternative of using a table for holding your constants despite the extra protection of CHECK constraints. Tables just aren’t designed to be immutable! As a spoiler, I’ll tell you that they perform as well as a view.

The Performance tests

Having lined up all the candidate solutions, let’s see how they perform. I’m going to test how quickly each of the options can find out how many five-letter words there are in common usage in the English language. The tests require us to create a simple Commonwords table consisting of a single column (primary key) of all the common words. To populate it, you’ll need to download the commonwords file, then run Listing 3, with the correct path to the file.

Listing 3

For the timings, I’m going to use the simple test harness described in my article, How to record T-SQL execution times using a SQL Prompt snippet.

Listing 4

When we run this, we verify that all the forms of using a constant in a query produce the same results. The times show very clearly what the problem is, and the dramatic extent of it

A graph is scarcely necessary to emphasize the horror of PE017 – Incorrect usage of const UDF. The way SQL Server executes a scalar UDF that isn’t schema-bound, and therefore unverified, is so cautious (for every row, it asks “is it still returning 5?“) that it is fifty times slower.

Apart from avoiding use of non-schema bound scalar UDFs, the test showed that on average there is no real difference in performance between any of the other ways of getting a constant value to a query. The query execution plan is the same in each case.

Now, we end by using a tear-down section to leave everything tidy in our test database.

Listing 5

Recommendations

If you use an unverified scalar function, then the query will be grindingly slow because you will be executing it on every row whether it has a parameter or not.

If you are faced with a vast tangle of inherited code that is using scalar UDFs as global constants, then redo them with schema binding. However, if these are global variables, which change infrequently in a live system, then I wouldn’t think of that alternative because you can’t alter a schema-bound function without temporarily altering every table that uses it in a constraint or computed column, to remove them, alter the function, and then replace the constraints and computed column.

Views or TVFs are more versatile, and so I’d me more inclined to use them to hold ‘global’ values. If these are changed, the change is logged because they require a DDL change. The only problem is that only scalar functions can be used in constraints or computed columns. If you use a table, then fine, but remember that changing a constant isn’t a DDL change and so you must set up access permissions to deny anyone the right to change, for example, the tax rate!

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in Hub

Documenting your Database with SQL Change Automation

It would be wrong to portray SQL Change Automation (SCA) as being suitable only for epic project deployments, of the sort described in my previous article. It can do smaller tasks as well. To demonstr...

Also in SQL Prompt

SQL Prompt Code Analysis: INSERT INTO a permanent table with ORDER BY (PE020)

The SQL query that is used to produce the result that is inserted into the permanent table has its order specified by an ORDER BY statement. Relational tables are not ordered, so the ORDER BY is meani...

Also in Product learning

Spoofing Realistic Credit Card Data for your Test Systems using Data Masker

Data protection and privacy regulations, ranging from GDPR to HIPPAA to PCI, among many others, put strict compliance requirements on the storage and use of personal and sensitive data, in any of your...

Also about static code analysis

Improving legacy code using SQL Prompt code analysis

Code analysis is a useful technique for identifying potential issues in your code base, and ensuring that it adheres to your pre-defined standards, so that you can release your software product with m...

Also about SQL code smells

SQL Prompt code analysis: avoid non-standard column aliases

There was a time when column aliases in SQL Server and Sybase were only declared by an assignment expression, and some people are still fond of the old way of doing it, in much the same way as some of...

  • david wright

    For whatever reason (don’t want to get into that here) we use functions to return constants. For obvious reasons, those functions are all schema bound, but version 9.1.0.4138 of SQL Prompt’s code analysis insists on flagging their use as a performance issue. Is this a feature?

    CREATE FUNCTION dbo.f () RETURNS int
    WITH SCHEMABINDING
    AS
    BEGIN
    RETURN 5;
    END;
    GO

    CREATE PROCEDURE p AS
    BEGIN
    SELECT answer = dbo.f() FROM sys.all_objects;
    END;
    GO

    https://uploads.disquscdn.com/images/0e641eab2d6d7bc547a1ae2a6fa7c98f66188e89686854b66c3385b608172db3.png

    • Phil Factor

      This will have no impact on performance, as far as I can see. You can opt to turn this particular rule off in SQL Prompt as you are aware of the issues. There is no shame attached to using schemabound functions as global constants as long as nobody ever subsequently thinks of turning them into global variables!
      I am confident that this rule PE017 should be amended slightly to check for schema-binding. If no schemabinding, then the rule is valid, if the function is in the metadata as being precise and deterministic, then it isn’t. It just needs to check the ObjectProperty ‘IsSystemVerified’

  • Pingback: SQL Code Smells - Simple Talk()