Archives

Archives / 2006 / December
  • Tag, I'm it.............

    Ok, so I got tagged by Jerry Dennany.  Well, I am not just going to dump 5 things out about me.  I am going to give u five things, but there is also going to be some discussion about them.  So, here goes, 5 things that you don't know about me:

  • 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:

     

    Average time

    Standard Deviation

    Mostly TSql (base)

    1x

    10 / 1x

    TSql Cursor

    1.42x

    .61x

    CLR with Generic List

    2.04x

    10.4x

    CLR with Datasets

    4.65x

    15.6x

     

    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

  • Is this http://wallyweblogs.asp.net ?

    I'm not trying to subvert anything, but given the amount of downtown that people have from technology over the holidays and my increase in available time, I am afraid that someone might complain and start asking themselves if this is http://wallyweblogs.asp.net.  We shall see................

  • Microsoft Zune Media Player - First Impressions

    My absolute first impression of the Microsoft Zune Media Player is that Microsoft has done a good job with the interface.  I am finding it simpler to use than my IPod Video video and it runs the video files for the podcast in the wmv format.  Guess what, the ASP.NET Podcast distributes the files as wmv and mp4 formats.  The only reason they are distributed as mp4 is because of the IPod.  I have to go thru several steps to get the format correct for the IPod.  With the Zune, I just download them and they are converted for me on the fly.  This is much simpler.  The one thing that I have not seen is the support for reading directly from a podcast feed.  Since this is a generation 1 product, my guess is that will be added sometime in the future.

  • Festivus is fast approaching

    Only a few hours until Festivus.  Oh, how I enjoy the airing of grievances.  I hope that your Festivus pole is up already.  Oh, the excitement is building..................

  • Lori McKinney jingle from Huntsville

    Lori sent me this jingle that was published in the Huntsville .NET / VS.NET mailing list.  I wish she would sing it for the podcast.

      (chorus)
      Oh there's no user groups quite like Huntsville has,
      VS NET, IT Pro, or Office fan,
      If you pine for geek fellowship or techno-talk,
      We get that in Rocket City, Alabam'
     
      (verses)
      We've had two speakers come from Tennessee, Ron C. and Wally M.,
      plus Greg Y., Marks B. and D. from north G-A,
      Russ F. and Joe and Blain came traveling up from Tampa's sunny shore,
      TechNet John came up from Texas, DE Doug did not neglect us.
     
      From the east coast, INETA Rock Stars Brian, Julie and Miguel,
      Up from Texas, Chris, and Russ from bright LA, (plus Wally M.)
      We so appreciate your insights and your coming all this way,
      From Atlantic to Pacific, thanks folks, you were all terrific.
     
      As for our locals, five guys stepped up, ably handling meeting chores,
      Thanks to Josh for January's launch event
      And then to Ben, Kevin and Eric and to Rodney, thanks again,
      With pride in them I'll be vocal, they're great talents right here local.
     
      Thanks to Todd M. who drove from Birmingham and Andrew from Mobile,
      Thanks to all who came to talk at Code Camp, too,
      The guys from CLS and Compuware and all the volunteers
      who taught folks from Valley Garden, our success, you've had a part in.
     
      As always, thank you goes to Intergraph for giving meeting space,
      and to Dominos and Pepsi for the nosh
      Thanks telerik, ADTran, CODE Magazine, VS Net Pro, and more
      Culminis and our INETA, your support could not be better!
     
      (final chorus)
      Since there's no place like home, we'll not meet this month,
      Happy Holidays, best wishes for next year
      Hope to see you all soon and together find
      that we'll all be running Vista on new gear!

  • Namespace change in ASP.NET 2.0 AJAX Release Candidate (Atlas)

    Just wanted to throw out the one major change that I have seen in the ASP.NET 2.0 AJAX Release Candidate that I have seen so far.  The namespaces have changed.  What was previously Microsoft.Web has become System.Web.  I think that this was a good to change to go ahead and move the code into what will most likely be the final namespace in the Orcas timeframe.

  • Advanced Intellect and Dave Wanta

    I wanted to give a shout out of thanks to Dave Wanta and Advanced Intellect.  Dave has written a component called AspNetEmail.  You may be asking yourself, "Email is built into .NET, so what?"  Well, getting calendaring done is kindof a pain.  I plugged in Dave's component and it just worked.  I had a few questions and Dave knew the answers before I even asked.  His components are well worth the money.

  • Why is Traffic to the ASP.NET Podcast up so much?

    Its been an interesting last month or so.  I've been watching traffic to the ASP.NET Podcast XML feed go way up.  I've been trying to figure out why?  Is it my sparking good looks, cute smile, sterling voice, or something else?  Well, I think I have figured out why.  Here is what I have found:

  • Xander Sherry

    There is no telling who you will run into.  On my flight from Dallas to Seattle, Xander Sherry was on my flight.  I didn't know what he looked like, but at the baggage claim in Seattle, he came up to me and introduced himself.  He ended up going to eat dinner with us last night.

  • Office 2007 spam

    I hate spammers.  I wonder how many other people are getting the Office 2007 spam.

  • ASPInsider Summit is coming up

    Original post: http://morewally.com/cs/blogs/wallym/archive/2006/11/30/512.aspx

    I’m really excited.  Next week, I am going to Redmond/Seattle, WA for the ASPInsider Summit.  Its Tuesday(Dec. 5th), Wednesday(Dec. 6th), and Thursday(Dec. 7th).  I love going to these types of events at Microsoft.  I get to see all the other ASPInsiders, yes even you Plip, like Phil Winstanley, Dave Sussman, David Penton, Scott Cate, Steve Smith, the Telligent Guys, Steve Orr, and everyone else.  It is great to meet with people that do SO much out in the industry.  I learn so much just being around them.

    The only problem is that I decided to travel through Dallas, TX on my way to Seattle.  I figured that this would be a better route than to go through Chicago due to ice and snow.  While the weather is forecast to be fine on Sunday when I travel, the weather in Dallas today is snow.  I figured that there would be snow in Chicago in December, but Dallas?!?  WTF is going on there. 

    Wally

    PS.  I hate naming people because someone will be “hurt” because they didn’t get named.  I honestly am excited about seeing everyone!