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
Now consider this example using the Or operator:
If FirstCondition = True Or SecondCondition = False Then
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
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
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
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
test1 (1 row(s) affected) test2 Msg 8134, Level 16, State 1, Line 15 Divide by zero error encountered. (1 row(s) affected)
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
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
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
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.