SQL 2005: .NET vs. T-SQL, the meaningless performance test

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 System
Imports System.Data.Sql
Imports 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 Function
End Class

The equivalent T-SQL code:

CREATE FUNCTION MySQLSeries (@value int)
RETURNS bigint
WITH EXECUTE AS CALLER
AS
BEGIN
declare @result bigint
set @result = 0

While @value > 0
BEGIN
set @result = @result + @value
set @value = @value - 1
END
return (@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.

 

Thanks,

Hans