.NET CLR vs T-SQL

Question

.NET CLR vs T-SQL

ritesh.saluja

SSCommitted

Points: 1704
More actions
December 19, 2008 at 5:31 am

#199163

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.

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

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]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

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....

ritesh.saluja SSCommitted Points: 1704 More actions · Answer 3

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.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

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]

ritesh.saluja SSCommitted Points: 1704 More actions · Answer 5

ritesh.saluja

SSCommitted

Points: 1704

December 20, 2008 at 5:36 am

#915732

thanks a ton !!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

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)

ritesh.saluja SSCommitted Points: 1704 More actions · Answer 7

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

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]

jsanborn-825510 Old Hand Points: 354 More actions · Answer 9

Thanks Mr. Young. Very nice and clear explanations.

Cheers,

Jennifer

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

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]