TSql vs. SQL CLR Performance Analysis

Original Post: http://morewally.com/cs/blogs/wallym/archive/2006/12/27/616.aspx

I went and spoke to the Michiana .NET User Group on November 21, 2006.  The talk was in South Bend, IN.  The subject of the talk was Sql Server 2005 CLR Objects.  Whenever I do the talk, I got get the question of “Which should I do?  TSql or CLR objects.”  Personally, my generic answer has always been, that if a developer can perform something in TSql, its best to do it in TSql.  If you can perform the necessary operations using TSql, in general the performance will be better.  This is before one takes into account the project overhead of managing the source code for stored procedures, triggers, and other database objects.

Once you decide that your scenario actually needs a CLR Object, what’s the best way to perform the operation?  Should this be done entirely within a CLR Object?  I have decided to take a look at this question based on the WebSearch spider and its use of CLR Objects to perform certain data operations.

Question: What is the best way to implement a CLR Object within a Sql Server 2005 Database?  Which will perform the best?

Comment: Terms like "best" are highly subjective.  As a result, the question that was answered is which performs the best.  Obviously, that type of question is very dependent on the scenario.  As a result, please test this in your own shop to get the best results for your application.

Short Answer:  Do as much as you possibly can in TSql.  Use CLR Objects judiciously.  Once the need is determined, perform as many operations in TSql and use CLR Objects only as absolutely needed.

Much Longer Answer: First off, not all situations are the same.  In the following example, we are looking at this from the basis standpoint of keeping data synchronized within a single database table.  To answer this question, I have built three different solutions to this problem:

• Mostly TSql.  A TSQL trigger which creates a cursor and calls out to some custom CLR Functions on an individual basis.
• TSql Cursor.  A TSQL trigger that creates a cursor that calls out to some custom CLR Functions through a look within the trigger.
• A CLR Trigger which uses a Generic List <String> to hold the pending data along with a SqlDataReader to iterate through data.  Afterwards, each url is updated by iterating through the Generic List.
• A CLR Trigger which uses a DataSet/DataTable to hold the data and then the Sql Data Adapter’s .Update method to transfer the data back.

The relative performance of each was measured:

Performance Analysis

Now that we have seen the performance differences of the four solutions, let’s try and analyze the differences between them. 

The “Mostly TSql” solution performs the best.  This is not a surprise.  TSql has been around for a number of years.  It has been embedded within SQL Server and has a highly optimized relationship with the database.    While embedding the CLR with the database, it provides its own overhead, so its not a shock the “Mostly TSql” solution performs the best.

The TSql Cursor solution takes 1.42 times as long as the “Mostly TSql” solution to perform the same work.  This is not surprising as cursors generally take longer to perform a given operation compared to comparable sql commands.

The real surprise in this test is the performance of the CLR trigger which used a Generic List to hold it’s the sql commands that are to be sent back to the database.  The Generic List Trigger has performed more than twice as fast as the Dataset Trigger.  Personally, I would think that the Dataset would be more optimized in this scenario, however, it does not perform as well.  This is probably due to much more overhead with than a ‘simpler” Generic List.

Note: BobB suggested this reason for the Dataset not performing as well:

-- IMHO --

Arrays (especially multi-dimensional arrays) *might* be faster if you're doing array-only processing, preferrable (sometimes) over gobs of temp tables. But using a heavyweight object like a DataSet takes memory management away from the SQL engine. SQL engine is always best when it managed all the data buffers.

-- IMHO --

How was the data created?  It is important to communicate how the above data was calculated. 

1.      In each situation, approximately 15 updates were performed against the 1000 records in the table.  The command is “UPDATE TBLSEARCHRESULTS SET SERVERNAME=NULL”

2.      When testing was performed, the triggers that were not being tested were not in installed on the database table.

3.      The first 5 updates were not used for the calculation.  This is due to the necessary startup and JIT operations that are necessary for the objects within the database.  The idea is to simulate a running system.  As a result, startup isn’t a major concern for this test.

4.      The sql commands AVG() and STDEV() were used to create the data in the above table.

The test platform for the scenario was:

• Laptop.
• 1.8 GHz Pentium 4.
• 1 gigabyte of RAM.
• 60 gigabyte hard drive.
• Windows 2003 Server with Service Pak 1 and upto date security updates through Windows Update.
• Sql Server 2005 Service Pak 1.

Areas of concern/problems

There are a number of issues and assumptions made within this test that could cause problems with the data that was generated.

A number of Assumptions have been made in this test:

• There is no need to go outside of the single database.  If there was a need to go outside of the database, this test would not be applicable to your needs.
• Only updates are tracked.  No inserts were tracked during this testing.
• This was tested using a laptop, not a multi-processor database server with an appropriate drive system layout.  Different results for different hardware layout would not be unexpected.
• I don’t believe I have any Sql Injection attack openings, but I might be wrong.

Note: Bob Beauchemin took a look at this before I published it.  Many thanks to him for taking the time to look at it and provide a couple of suggestions and insight.

The Source Code

The source code for the three scenarios is included below.

TSQL Trigger

USE [dbWebSearchYukon]

GO

/****** Object:  Trigger [dbo].[Trigger1]    Script Date: 11/28/2006 11:42:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[Trigger1]

ON [dbo].[tblSearchResults]

FOR INSERT

AS

BEGIN

            UPDATE TBLSEARCHRESULTS SET

                        SEARCHCODE=DBO.CALCULATESEARCHCODE(SEARCHURL),

                        SERVERNAME=DBO.CALCULATESERVERNAME(SEARCHURL),

                        DATEENTERED=GETDATE(),

                        DATEUPDATED=GETDATE()

                        WHERE TBLSEARCHRESULTSID IN (SELECT TBLSEARCHRESULTSID FROM INSERTED)

END

USE [dbWebSearchYukon]

GO

/****** Object:  Trigger [dbo].[Trigger2]    Script Date: 11/28/2006 11:43:29 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[Trigger2]

ON [dbo].[tblSearchResults]

FOR UPDATE

AS

BEGIN

            UPDATE TBLSEARCHRESULTS SET

                        SEARCHCODE=DBO.CALCULATESEARCHCODE(SEARCHURL),

                        SERVERNAME=DBO.CALCULATESERVERNAME(SEARCHURL),

                        DATEUPDATED=GETDATE()

                        WHERE TBLSEARCHRESULTSID IN (SELECT TBLSEARCHRESULTSID FROM INSERTED)

END

TSql Cursor

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[TR_TBLSEARCHRESULTS_UPDATE] ON [dbo].[tblSearchResults] FOR UPDATE

AS

BEGIN

            SET NOCOUNT ON;

            DECLARE @SEARCHURL VARCHAR(4096)

            DECLARE @SEARCHRESULTSID INT

            DECLARE SEARCHRESULTSCURSOR CURSOR FOR SELECT TBLSEARCHRESULTSID, SEARCHURL FROM INSERTED

            OPEN SEARCHRESULTSCURSOR

            FETCH NEXT FROM SEARCHRESULTSCURSOR INTO

                        @SEARCHRESULTSID, @SEARCHURL

            WHILE @@FETCH_STATUS = 0

            BEGIN

                        UPDATE TBLSEARCHRESULTS SET SERVERNAME=DBO.CALCULATESERVERNAME(@SEARCHURL),

                                    SEARCHCODE=DBO.CALCULATESEARCHCODE(@SEARCHURL), DATEUPDATED=GETDATE() WHERE TBLSEARCHRESULTSID=@SEARCHRESULTSID

                        FETCH NEXT FROM SEARCHRESULTSCURSOR INTO @SEARCHRESULTSID, @SEARCHURL

            END

            CLOSE SEARCHRESULTSCURSOR

            DEALLOCATE SEARCHRESULTSCURSOR

END

VB.NET CLR Functions

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions

    <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, SystemDataAccess:=SystemDataAccessKind.None)> _

    Public Shared Function CalculateServerName(ByVal pstrUrl As String) As String

        Dim strTemp As String

        Dim strHttp As String = "http://"

        Dim iLengthHttp As Integer = strHttp.Length

        Dim iLengthTillSlash As Integer = pstrUrl.IndexOf("/", iLengthHttp, pstrUrl.Length - iLengthHttp)

        If (iLengthTillSlash = -1) Then

            strTemp = pstrUrl.Substring(strHttp.Length, pstrUrl.Length - iLengthHttp)

        Else

            strTemp = pstrUrl.Substring(iLengthHttp, iLengthTillSlash - iLengthHttp)

        End If

        Return (strTemp)

    End Function

    <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, SystemDataAccess:=SystemDataAccessKind.None)> _

    Public Shared Function CalculateSearchCode(ByVal pstrurl As String) As Long

        Return (pstrurl.GetHashCode())

    End Function

End Class

VB.NET Trigger with Dataset

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class Triggers

    ' Enter existing table or view for the target and uncomment the attribute line

    <SqlTrigger(Name:="tr_tblSearchResults_InsertUpdate1", Event:="FOR INSERT, UPDATE", Target:="tblSearchResults")> _

    Public Shared Sub Trigger1()

        Dim strSql As String = String.Empty

        Dim glResults As New Generic.List(Of String)

        Dim sqlCn As New SqlConnection("Context Connection=true")

        Dim sqlCmd As New SqlCommand

        Dim sqlUpd As New SqlCommand

        Dim sqlInsert As New SqlCommand

        Dim sqlTrCtx As SqlTriggerContext = SqlContext.TriggerContext

        Dim sqlDa As New SqlDataAdapter()

        Dim dtData As New DataTable

        Try

            strSql = "select SearchUrl, tblSearchResultsId, SearchCode, ServerName, DateEntered, DateUpdated from tblSearchResults where tblSearchResultsId in (select tblSearchResultsId from inserted)"

            sqlCmd.Connection = sqlCn

            sqlCn.Open()

            sqlCmd.CommandText = strSql

            sqlCmd.CommandType = CommandType.Text

            sqlDa.SelectCommand = sqlCmd

            sqlDa.Fill(dtData)

            For Each drData As DataRow In dtData.Rows

                drData("SearchCode") = CalculateSearchCode(Convert.ToString(drData("SearchUrl")))

                drData("ServerName") = CalculateServerName(Convert.ToString(drData("SearchUrl")))

                drData("DateUpdated") = DateTime.Now

                If sqlTrCtx.TriggerAction = TriggerAction.Insert Then

                    drData("DateEntered") = DateTime.Now

                End If

            Next

            sqlUpd.Parameters.Add("@SearchCode", SqlDbType.Int, Nothing, "SearchCode")

            sqlUpd.Parameters.Add("@ServerName", SqlDbType.VarChar, 100, "ServerName")

            sqlUpd.Parameters.Add("@DateUpdated", SqlDbType.DateTime, Nothing, "DateUpdated")

            sqlUpd.Parameters.Add("@tblSearchResultsId", SqlDbType.Int, Nothing, "tblSearchResultsId")

            sqlInsert.Parameters.Add("@SearchCode", SqlDbType.Int, Nothing, "SearchCode")

            sqlInsert.Parameters.Add("@ServerName", SqlDbType.VarChar, 100, "ServerName")

            sqlInsert.Parameters.Add("@DateUpdated", SqlDbType.DateTime, Nothing, "DateUpdated")

            sqlInsert.Parameters.Add("@DateEntered", SqlDbType.DateTime, Nothing, "DateEntered")

            sqlInsert.Parameters.Add("@tblSearchResultsId", SqlDbType.Int, Nothing, "tblSearchResultsId")

            sqlUpd.CommandText = "update tblSearchResults set SearchCode=@SearchCode, ServerName=@ServerName, DateUpdated=@DateUpdated where tblSearchResultsId=@tblSearchResultsId"

            sqlUpd.Connection = sqlCn

            sqlInsert.CommandText = "insert into tblSearchResults (SearchCode, ServerName, DateEntered, DateUpdated) values (@SearchCode, @ServerName, @DateEntered, @DateUpdated)"

            sqlInsert.Connection = sqlCn

            sqlDa.UpdateCommand = sqlUpd

            sqlDa.InsertCommand = sqlInsert

            sqlDa.Update(dtData)

        Catch sqlExc As SqlException

            Dim sqlPipe As SqlPipe = SqlContext.Pipe

            sqlPipe.Send("Error: " & sqlExc.Message.ToString())

            sqlPipe = Nothing

            Throw

        Finally

            If sqlCn.State <> ConnectionState.Closed Then

                sqlCn.Close()

            End If

            sqlCmd.Dispose()

        End Try

    End Sub

    Private Shared Function CalculateServerName(ByVal pstrUrl As String) As String

        Dim strTemp As String

        Dim strHttp As String = "http://"

        Dim iLengthHttp As Integer = strHttp.Length

        Dim iLengthTillSlash As Integer = pstrUrl.IndexOf("/", iLengthHttp, pstrUrl.Length - iLengthHttp)

        If (iLengthTillSlash = -1) Then

            strTemp = pstrUrl.Substring(strHttp.Length, pstrUrl.Length - iLengthHttp)

        Else

            strTemp = pstrUrl.Substring(iLengthHttp, iLengthTillSlash - iLengthHttp)

        End If

        Return (strTemp)

    End Function

    Private Shared Function CalculateSearchCode(ByVal pstrurl As String) As Long

        Return (pstrurl.GetHashCode())

    End Function

End Class

VB.NET Trigger with Generic List

Option Explicit On

Option Strict On

Imports System

Imports System.Data.Sql

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports System.Diagnostics

Imports Microsoft.SqlServer.Server

Partial Public Class Triggers

    <SqlTrigger(Name:="tr_tblSearchResults_InsertUpdate", Event:="FOR INSERT, UPDATE", Target:="tblSearchResults")> _

    Public Shared Sub InsertUpdateSearchResultsTrigger()

        Dim i As Integer

        Dim iSearchCode As Long

        Dim strServerName As String

        Dim strSql As String = String.Empty

        Dim glResults As New Generic.List(Of String)

        Dim sqlCn As New SqlConnection("Context Connection=true")

        Dim sqlCmd As New SqlCommand

        Dim sqlTrCtx As SqlTriggerContext = SqlContext.TriggerContext

        Dim dsData As New DataSet()

        Dim dtData As New DataTable

        Dim sqlDr As SqlDataReader

        Try

            strSql = "select SearchUrl, tblSearchResultsId from inserted"

            sqlCmd.Connection = sqlCn

            sqlCn.Open()

            sqlCmd.CommandText = strSql

            sqlCmd.CommandType = CommandType.Text

            sqlDr = sqlCmd.ExecuteReader()

            While sqlDr.Read()

                iSearchCode = CalculateSearchCode(Convert.ToString(sqlDr("SearchUrl")))

                strServerName = CalculateServerName(Convert.ToString(sqlDr("SearchUrl")))

                strSql = "update tblSearchResults set SearchCode=" & iSearchCode.ToString() & ", ServerName='" & _

                    strServerName.Replace("'", "''") & "', DateUpdated=getdate() where tblSearchResultsId=" & _

                    Convert.ToString(sqlDr("tblSearchResultsId"))

                If sqlTrCtx.TriggerAction = TriggerAction.Insert Then

                    strSql = strSql & ";update tblSearchResults set DateEntered=getdate() where tblSearchresultsId=" & _

                        Convert.ToString(sqlDr("tblSearchResultsId"))

                End If

                glResults.Add(strSql)

            End While

            If Not (sqlDr.IsClosed) Then

                sqlDr.Close()

            End If

            For i = 0 To (glResults.Count - 1)

                sqlCmd.CommandText = glResults.Item(i)

                sqlCmd.ExecuteNonQuery()

            Next

            sqlDr.Dispose()

        Catch sqlExc As SqlException

            Dim sqlPipe As SqlPipe = SqlContext.Pipe

            sqlPipe.Send("Error: " & sqlExc.Message.ToString())

            sqlPipe = Nothing

            Throw

        Finally

            If sqlCn.State <> ConnectionState.Closed Then

                sqlCn.Close()

            End If

            sqlCmd.Dispose()

        End Try

    End Sub

    Private Shared Function CalculateServerName(ByVal pstrUrl As String) As String

        Dim strTemp As String

        Dim strHttp As String = "http://"

        Dim iLengthHttp As Integer = strHttp.Length

        Dim iLengthTillSlash As Integer = pstrUrl.IndexOf("/", iLengthHttp, pstrUrl.Length - iLengthHttp)

        If (iLengthTillSlash = -1) Then

            strTemp = pstrUrl.Substring(strHttp.Length, pstrUrl.Length - iLengthHttp)

        Else

            strTemp = pstrUrl.Substring(iLengthHttp, iLengthTillSlash - iLengthHttp)

        End If

        Return (strTemp)

    End Function

    Private Shared Function CalculateSearchCode(ByVal pstrurl As String) As Long

        Return (pstrurl.GetHashCode())

    End Function

End Class