When I get in the office Monday, a question of a friend (Nicolas) was awaiting in my inbox. His question was about the IsNull function in SQL and whether or not it was doing short-circuiting or not. I quickly pressed the reply button, wrote something and just before clicking the send button, I had a doubt!
Notice that this article focuses more on SQL Server then VB but it is information that every developer should be aware of.
Definition
But before starting, do you know what short-circuiting is and how it can help you? Do you know that short-circuiting exist in VB.Net, C#, T-SQL and many more?
Wikipedia defines “short-circuiting evaluation” like this:
Short-circuit evaluation or minimal evaluation denotes the semantics of some boolean operators in some programming languages in which the second argument is only executed or evaluated if the first argument does not suffice to determine the value of the expression: when the first argument of and evaluates to false, the overall value must be false; and when the first argument of or evaluates to true, the overall value must be true. In some programming languages (Lisp), the usual boolean operators are short-circuit. In others (C, Ada), both short-circuit and standard boolean operators are available.
In short, conditions are only evaluated if required. Let see some examples.
Some VB examples
Consider this example using the And operator:
If FirstCondition = False And SecondCondition = False Then
Is there a need to evaluate the SecondCondition here? Not at all because we already know that the first test returns false and the whole evaluation will return false. You might say that it is not worth asking the question but what if the SecondCondition is a call to a function that retrieves a value from a database? You might save precious time and resources.
Now consider this example using the Or operator:
If FirstCondition = True Or SecondCondition = False Then
Again, because the first condition returns true, there is no need to evaluate the second condition because whatever its value is, the whole evaluation will return true.
Before the .Net era, the legacy versions of VB (up to VB6) never had short-circuiting evaluation meaning that all the conditions were evaluated before returning the final answer even if not required. When the first beta version of VB.Net (back in 2002), Microsoft thought it was time to fix it so they have changed the behaviour of And operator and the Or operator and so they decided to replace the VB-Legacy behaviour of the And/Or operators in favour of a behaviour offering short-circuiting evaluation. And then came feedbacks... So many people complained about this change of behaviour that Microsoft decided to revert to the standard behaviour and created 2 new operators that you now (I really hope) always use: AndAlso and OrElse.
You can really test this behaviour if you create a very small function like this one:
Private Function F1(ByVal P1 As Integer) As Integer
Return P1
End Function
Nothing is fancy here, just a function that accepts an integer argument and returns it. It is just enough to be able to use the debugger to step through it and discover if the function is executed or not. So if you add code like this: If F1(1) = 0 And F1(2) = 2 Then
MessageBox.Show("Both conditions are True")
Else
MessageBox.Show("At least one condition returns False")
End If
By placing a breakpoint into the F1 function and running this code, the breakpoint will be hit twice because both conditions will be executed.
Now if you replace the And operator with the AndAlso operator and run the code again, the F1 function will only be executed once.
You can also do the same test with the Or and the OrElse operators.
Why does VB has 2 operators?
Most (if not all) languages I know all use short-circuiting. VB is the only one to offer if probably because it was an interpreted language a long time ago.
As I told before, Microsoft wanted to change the behaviour of the And/Or operators but many programmers complained. Why did people complain would you ask? For portability reasons. Some applications migrated from VB6 to VB.Net were not working like they used to just because of those operators. If my F1 function would have been something useful and meaningful that would have done some process affecting global variables or updating a database, executing it once or twice (even if the returning result make the condition returns False) makes a big difference. It is not in best practices to work like this but some people do. I would have prefer Microsoft to create new operators for them instead of having us all to take the habit of using new operators but that is just my opinion.
And now in SQL Server
If you remember the introduction, the question of my friend was about SQL Server. So far, I have only talked about VB and C# (I just couldn’t resist!). If you search the help system, you will read that SQL supports short-circuiting but doing my own set of test, I have found some exceptions to that statement.
For example, consider those 3 requests:
select 'short circuit' where 1=0 and 1/0 = 0
select 'short circuit' where 1=1 and 1/0 = 0
select 'short circuit' where 1/0 = 0 AND 1=0
If you were to pick a guess, what do you think the result would be? I would just like to bring your attention that all 3 queries have the potential of returning a “divide by zero error”. Surprisingly, only the second query returns this error. That means that SQL id definitively able of doing short-circuiting but not necessarily from left to right (the third query has the faulty condition first).
Now that I clearly showed you that short-circuiting is supported by SQL Server (by the way, I am using SQL Server 2005 SP2 for this demo), I will show you that there are some places where it is not supported and that may affect your performance.
Testing the IsNull function
Now, will do some test with those queries:
DECLARE @test INT
PRINT 'test1'
SET @test = 1
SET @test = ISNULL(@test, 1/0)
SELECT 'test1', @test
PRINT 'test2'
SET @test = NULL
SET @test = ISNULL(@test, 1/0)
SELECT 'test2', @test
It is time for you to guess the results. The results that you will find in the message window are the following: test1
(1 row(s) affected)
test2
Msg 8134, Level 16, State 1, Line 15
Divide by zero error encountered.
(1 row(s) affected)
Here we see that the first test is not doing the operation of the second operator of the IsNull function or that at least the error is trapped and ignored. So far so good, it is behaving as expected.
Testing the IsNull function with a UDF
We will no do another test but this time we will put a call to a User-Defined Function as the second argument of the IsNull function. To be able to demonstrate it, we need a UDF. Why not this very simple one:
CREATE FUNCTION dbo.UDF1()
RETURNS INTEGER
AS
BEGIN
RETURN 55
END
This dummy function accepts no parameter and returns a constant value of 55 (which has no special meaning). Now to test it, you can use simple queries like this: DECLARE @test INT
SET @test = 1
PRINT 'test1'
SET @test = ISNULL(@test, (SELECT dbo.UDF1()))
SELECT 'test1', @test
SET @test = NULL
PRINT 'test2'
SET @test = ISNULL(@test, (SELECT dbo.UDF1()))
SELECT 'test2', @test
Running those queries displays expected results. But the real question is that the first query has a non null value as the first argument, does the UDF is executed or not?
To be able to answer that question and to be really able to demonstrate it without any doubt, I have used the Visual Studio debugger. Using the Server Explorer, I have added a data connection to connect to a SQL Server, created a new stored procedure in which I copied the last queries (the ones that call the UDF), and saved the stored procedure. Now, I am ready to right-click in my stored procedure and select “Step into stored procedure”. Using this process, I was able to confirm that the UDF is executed even when not required. So the short-circuiting is clearly not working here.
If you didn’t know that we can use the Visual Studio debugger to trace T-SQL stored procedures and UDFs, you have another revelation. It can be complex to set up but it worth it.
Another test with the IsNull function
I then wanted to know if only the UDFs were not working with the short-circuiting. So I tried these other queries:
DECLARE @test INT
SET @test = 1
PRINT 'test1'
SET @test = ISNULL(@test, (SELECT COUNT(*) FROM Categories))
SELECT 'test1', @test
SET @test = NULL
PRINT 'test2'
SET @test = ISNULL(@test, (SELECT COUNT(*) FROM Categories))
SELECT 'test2', @test
To be able to find if the second arguments was executed or not, I simply select “Include actual execution plan” from the Query menu of the SQL Server Management Studio IDE. When looking at the execution plan, we can clearly see that the Categories table is used in each plan. Sadly short-circuiting is not effective here.
Figure 1: The execution plan
In order to complete my testing on T-SQL, I have tried the same test with COALESCE. Not surprisingly, the results are the same as those obtained with IsNull.
Conclusion
Short-circuiting can really improve performance but it is sadly not supported everywhere. When it is not supported (like in many T-SQL statements shown here), you better rewrite your queries not to execute statements when not required.
You will find in the download attached to this article, the VB.Net source code and the T-SQL queries.