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):
Imports SystemImports System.Data.SqlImports System.Data.SqlTypes
Public Class CMyNetSeries <SqlFunc()> _ 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 End While Return New SqlInt64(result) End FunctionEnd Class
The equivalent T-SQL code:
CREATE FUNCTION MySQLSeries (@value int)RETURNS bigintWITH EXECUTE AS CALLERASBEGINdeclare @result bigint set @result = 0
While @value > 0 BEGIN set @result = @result + @value set @value = @value - 1 ENDreturn (@result)end
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...select dbo.myseries(30000)select dbo.mynetseries(30000)
You'll see no difference. Both return results within the second on my machine.
When you try this:select dbo.myseries(300000000)select dbo.mynetseries(300000000)
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.