CLR vs. T-SQL and Stored Procedures in SQL Server 2005

CLR or TSQL? That is the question.

More are more developers are struggling to find the right answer, but the answer really depends on the developerís individual needs.

For classic SQL tasks, the good old TSQL is recommended. On the other hand, CLR works best for calculations, parsing, image processing and other tasks that deal with a very limited amount of data.

We performed an experiment that defies the common perception that calculation tasks run several times faster when implemented in CLR form. For this experiment, we utilized a computer with Pentium 4 2.4GHZ processor and 1 Gb of RAM.

We created a very simple user defined function on T-SQL that adds two numbers. The function is found below:

CREATE FUNCTION FuncSum(@n1 INT, @n2 INT)
  RETURNS INT
AS
BEGIN
    RETURN @n1 + @n2
END

Then, we rewrote the same function using CLR this time. This looks a little bit more complicated.

using System;
using Microsoft.SqlServer.Server;

public class TestProcedures
{
    [Microsoft.SqlServer.Server.SqlFunction( 
    IsDeterministic = true, DataAccess = DataAccessKind.Read )]
    [CLSCompliant( false )]
    public static int FuncSum_CLR(int n1, int n2)
    {
        return n1 + n2;
    }
}

After which, we loaded them into our database:

CREATE ASSEMBLY SQL2005_Assembly
    FROM 
'C:\Documents and Settings\User\My Documents\Visual Studio 2005\Projects\
SQL2005_Assembly\bin\Release\SQL2005_Assembly.dll'
    WITH PERMISSION_SET = UNSAFE

CREATE FUNCTION FuncSum_CLR
    (
        @n1 int, @n2 int
    )
    RETURNS int
    AS EXTERNAL NAME SQL2005_Assembly.TestProcedures.FuncSum_CLR

We then ran the TSQL and CLR function 100,000 times over and compared the result:

CREATE PROCEDURE TestFuncSum(_CLR)
AS
BEGIN
    DECLARE @t datetime
    SET @t = getdate()
    DECLARE @n INT
    DECLARE @i INT
    SET @i = 0
    WHILE (@i <100000)
    BEGIN
        EXEC @n = FuncSum(_CLR) 1,1
        SET @i = @i + 1
    END
    SELECT datediff(ms, @t, getdate())
END

The results were revealing:

• TSQL: 2000ms
• CLR: 6300ms

The experiment proved that CLR was running more than three times slower on a mere calculation task. Hereís the explanation:

Obviously, it takes time for the SQL server to switch the context from the kernel to the CLR (.Net framework). This time in that experiment can be estimated using formula (6300-2000)/100000 = 0.042ms. It is almost nothing in comparison with other executions, remember, even SQL profiler does not detect time periods less then 13-16ms.

However, it might be important if you are using CLR functions in WHERE conditions or as a parameters to the aggregation functions, which are called thousands of times.

This overhead is the same for TSQL and CLR procedures.