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

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

  • Comments 12

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

 

  • 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
  • T-SQL vs CLR will be a "hot" debate very soon and it will be a very long time till T-SQL will be replaced (unfortunately)..
  • 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.
  • 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
  • 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.
  • 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.
  • Hi Jerry,

    We are nearing completion of development and testing of Beta 2. Beta 2 release is targeted for the coming weeks. As I understand it, Beta 2 will be more open than beta 1. This is not to say that it will be completely public. Beta 3, which should be completed near the end of 2004, will be even more widely available. I'll blog the details of beta availability once they are announced.
  • 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.
  • PingBack from http://chaespot.com/mssql/2006/09/26/t-sql-in-y0mbos-tags-on-magnolia/
  • PingBack from http://chaespot.com/mssql/2007/04/11/vba-vbnet-t-sql-code-samples-join-the/

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

  • PingBack from http://jasonhome.007gb.com/creatingfunctionssql2005.html

Page 1 of 1 (12 items)