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

Comments

• Anonymous
  June 22, 2004
  Hans,
  
  I am sure that for easy code, T-SQL outperforms the CLR because there is no extra hassle to call CLR code. (which includes extra assembly loading, ...) This will be the bulk of the actions out there.
  
  As soon as a great many instructions or complex instructions are to be executed, I am sure the CLR wins every time.
  
  Best regards,
  Dirk

• Anonymous
  June 22, 2004
  The comment has been removed

• Anonymous
  June 22, 2004
  Why unfortunatelly? CLR will be faster in a non-database stuff, such as adding up a list of numbers. That's pretty obvious but it will lead to exactly the wrong thing - more and more things will be done in the database that shouldn't.
  
  Btw it's gotta be pretty easy to write T-SQL code that outperforms CLR. Just use a query (a database query, it's a friggin SQL server) that does couple joins and T-SQL should come out much faster than CLR.

• Anonymous
  June 22, 2004
  It's not that hard to show when T-SQL will be signifigantly faster than .NET:
  
  update a million rows with a single T-SQL statement: UPDATE Customer SET IsActive=0
  
  versus a .Net code snippet that [In theory] would have to iterate the entire table
  foreach DataRow dr in customersTable
  dr["IsActive"]=0
  
  since T-Sql can be executed directly by the db engine, T-Sql should be a good magnitude faster

• Anonymous
  June 22, 2004
  To make things clear. My point is that this is a matter of choice. People should use T-SQL where it makes sense and .NET code where it makes sense. Simple data access, go for T-SQL. Chose .NET for ore complex logic, enforcement of business rules that benefit from being enforced close to the data, any code where you benefit from the richness of the .NET class library, those stored procs where you use 'T-SQL' server side cursors, etc...
  Nevertheless, I don't think the do a simple update is the right sample. I can take the same update statement, assign it to my command object and then executescalar. Although largely unnecessary, I'm using .NET here with very little - if not none at all - benefit. But with this sample, I still can't show that T-SQL is faster then .NET because the in-and-out to the CLR is neglectible when compared to the time it takes to update the rows. So I still have no simple sample to show... Thanks.

• Anonymous
  June 22, 2004
  Hans, complex logic that has nothing to do with a database should not be executed under a database server. Whatever you will do in CLR in Yukon is code that should not be there in the first place.
  
  If you tell me/us where to download Yukon I'll come up with data queries that will be faster in T-SQL than CLR.

• Anonymous
  June 23, 2004
  The comment has been removed

• Anonymous
  June 23, 2004
  If you can get me on the beta tester list (I have a MS beta testing account) I'll be more than happy to try it out and try to come up with T-SQL that's an order of magnitude faster than its CLR equivalent.

• Anonymous
  September 25, 2006
  PingBack from http://chaespot.com/mssql/2006/09/26/t-sql-in-y0mbos-tags-on-magnolia/

• Anonymous
  April 11, 2007
  PingBack from http://chaespot.com/mssql/2007/04/11/vba-vbnet-t-sql-code-samples-join-the/

• Anonymous
  May 07, 2007
  PingBack from http://chaespot.com/mssql/2007/05/07/got-to-talking-with-my-banker-and-she/

• Anonymous
  July 22, 2008
  PingBack from http://jasonhome.007gb.com/creatingfunctionssql2005.html