.NET CLR vs T-SQL

  • I want to know the scenarios where a managed .Net procedure (.NET CLR integration with SS2k5) will perform better than one written in T-SQL

    could anyone please guide.

  • IMHO SQLCLR only outperforms T-SQL in two general cases:

    1) In cases where the same activation path must be followed by both (Triggers, Service Broker activation procs) then CLR appears to perform comparably or better (though that probably depends on how much data interaction is required).

    2) In cases where the CLR-required activation path can be bypassed by in-lining T-SQL (user defined functions and stored procedures) CLR can only equal or beat the speed of T-SQL:

    if the amount of calculation per activation is very high, or is much higher for T-SQL than it is for CLR and the additional data access required (outside of the passed parameters) is very low or

    if CLR enables a lower-order query than T-SQL can achieve (such as reduce a Triangular Join to a Linear one)

    In practice (2) hardly ever happens.

    There is also a third general case to consider:

    3) Cases where SQLCLR has an activation path that T-SQL cannot use (User Defined Aggregates, User Defined Type methods). These cannot necessarily be said to outperform T-SQL, because they are things that you cannot even do them with T-SQL, though you might be able to approach the problem in a different way that can use T-SQl.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung (12/19/2008)


    IMHO SQLCLR only outperforms T-SQL in two general cases:

    1) In cases where the same activation path must be followed by both (Triggers, Service Broker activation procs) then CLR appears to perform comparably or better (though that probably depends on how much data interaction is required).

    2) In cases where the CLR-required activation path can be bypassed by in-lining T-SQL (user defined functions and stored procedures) CLR can only equal or beat the speed of T-SQL:

    if the amount of calculation per activation is very high, or is much higher for T-SQL than it is for CLR and the additional data access required (outside of the passed parameters) is very low or

    if CLR enables a lower-order query than T-SQL can achieve (such as reduce a Triangular Join to a Linear one)

    In practice (2) hardly ever happens.

    There is also a third general case to consider:

    3) Cases where SQLCLR has an activation path that T-SQL cannot use (User Defined Aggregates, User Defined Type methods). These cannot necessarily be said to outperform T-SQL, because they are things that you cannot even do them with T-SQL, though you might be able to approach the problem in a different way that can use T-SQl.

    What he said....

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Thanks RBarryYoung

    Could you please explain more in detail with short example.

    Ex. You wrote "In cases where the same activation path must be followed by both". I am unable to understand this.

    similarly "In cases where the CLR-required activation path can be bypassed by in-lining T-SQL", could you please explain this with some examples showing the CLR requires activation path or same activation path is followed by both etc.

    example would give me better understanding.

  • ritesh.saluja (12/19/2008)


    Ex. You wrote "In cases where the same activation path must be followed by both". I am unable to understand this.

    ...

    similarly "In cases where the CLR-required activation path can be bypassed by in-lining T-SQL",

    Mostly what I am getting at here is that activation/invocation of code, be it SQL or CLR, has substantial overhead. If there is a way for SQL code to be used, without having to activate or invoke it separately, then that will almost always be faster.

    For instance, we could write a function in T_SQL to add two numbers together:

    CREATE Function dbo.fnAdd (@A as int, @b-2 as int)

    Returns int As

    Begin

    Return @a+@B

    End

    We could also write an equivalent VB.net function like so:

    Public function dnAdd(A as Long, B as Long) as Long

    Return A + B

    End Function

    Now if we write a query that uses them like so:

    Select ID, dbo.fnAdd(colA, colB) as [Total]

    From MillionRowTable

    and

    Select ID, dbo.dnAdd(colA, colB) as [Total]

    From MillionRowTable

    both will be similarly slow because both will have to be invoked or activated 1 million times (according to Microsoft, the CLR will be slightly faster, though I have not tested it). This is misleading though, because with T-SQL you have an option to get around this overhead:

    Select ID, (colA + colB) as [Total]

    From MillionRowTable

    This is called "in-lining", because you are putting the fnAdd() functions code "in-line" with the rest of the SQL code.

    Because this gets rid of the activation overhead (and also because the SQL compiler can optimize it better) it is almost always faster than either T-SQL or CLR in Functions or Stored Procedures. Finally, because this option is obviously only available to T-SQL, it means that CLR in Procedures and Functions can almost never beat T-SQL for the simple reason that the T-SQL does not have to be in a separate Function or Stored Procedure.

    Triggers and Service Broker activation procedures are different, because some kind of "activation" or "invocation" must occur, so there is not way for the T-SQL to get around this overhead.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • thanks a ton !!

  • From a much less technical stance...

    The only thing I haven't been able to do faster in T-SQL, so far, is RegExReplace and I still came pretty close.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Hi RBarryYoung

    You mentionedin the first post that same activation path must be followed by both CLR and T-SQL in case of Triggers, Service Broker activation procs.

    could you please explain this as well in more detail

  • I already did explain this:

    Triggers and Service Broker activation procedures are different, because some kind of "activation" or "invocation" must occur, so there is no way for the T-SQL to get around this overhead.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thanks Mr. Young. Very nice and clear explanations.

    Cheers,

    Jennifer

  • Glad I could help.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply