When talking about SQL Server 2005 – CLR integration I consistently got the question: ‘Which code runs faster? T-SQL or IL’. The answer is obviously: ‘It depends’.
Those people who downloaded the samples of my presentation at TechEd Israel, already saw ‘The Meaningless Performance Test’ I showed over there. For those who didn’t, let me repeat it here since I would like someone to create a performance test with an outcome that is opposite of this one.
The code that I wrote is really simple. It’s a user defined function in which you pass an integer, let’s say five and then the function returns 5+4+3+2+1. Pass in 3 and the function returns 3+2+1. Easy.
The .NET code to achieve this is the following (obviously this needs to be deployed in SQL after building the assembly):
Public Class CMyNetSeries
Public Shared Function MyNetSeries(ByVal value As SqlInt32) As SqlInt64
Dim input As Integer = value.Value
Dim result As Double
While input > 0
result = result + input
input -= 1
Return New SqlInt64(result)
The equivalent T-SQL code:
CREATE FUNCTION MySQLSeries (@value int)
WITH EXECUTE AS CALLER
declare @result bigint
set @result = 0
While @value > 0
set @result = @result + @value
set @value = @value – 1
After creating these functions, it’s easy to compare performance. Just run them in the workbench and see how long it takes to execute.
When you try this…
You’ll see no difference. Both return results within the second on my machine.
When you try this:
BIG difference. .NET code returns result within the second while the T-SQL function takes 11 seconds.
Obviously, I don’t want to conclude much from this meaningless test. I’m running beta1 code in a Virtual PC image. What more do you need to know?
However, what I’m really looking for is a simple test that would show that T-SQL is better at performing some type of operation than .NET. I was thinking pure data access would do the trick but my samples so far don’t show any difference between .NET code and pure T-SQL code. There must be many examples that prove that T-SQL outperforms .NET code in specific scenarios. I would be thankful if you could share it if you have one.