CLR Funtions

Question

CLR Funtions

manoj2001

SSCrazy

Points: 2225
More actions
May 15, 2009 at 2:22 am

#209516

Hi All,
i want to ask you one question to all expertise who are using 2005 with CLR.
Is there any performance problem you are facing while using your SPs Functions
with CLR integration in it.
My question is is there any performance drawback using CLR in SQl Server?
Manoj

Viewing 8 posts - 1 through 7 (of 7 total)

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

Tom257 SSChampion Points: 11543 More actions · Answer 1

In my experience CLR functions are faster than the equivalent SQL - However I've only used CLR functions for very complex queries that lend themselves to procedural processeing rather than set-based stuff.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

manoj2001 (5/15/2009)
Hi All,
i want to ask you one question to all expertise who are using 2005 with CLR.
Is there any performance problem you are facing while using your SPs Functions
with CLR integration in it.
My question is is there any performance drawback using CLR in SQl Server?
Manoj

Yes, definitely. In General CLR is not faster than in-line SQL, it's slower. There are a variety of reasons for this, but it boils down to the overhead of the call and return and certain limitations in the call interface, particularly, not being able large data sets/objects in every case that you might want to.

Now, encapsulated SQL routines have the same or greater overhead, but SQL has an option that CLR does not to overcome this, in-lining. For instance you can avoid the overhead of calling a SQL-based scalar UDF (user-defined function) by simply including the SQL code "in line" in the calling query itself. Obviously you cannot do this with CLR.

Not so obvious are other options in SQL that have the same effect. Views, CTEs (common table expressions), and SQL-based inline TVFs (table-valued functions) are not treated as encapsulated routines in SQL, but rather as expression macros that are effectively in-lined at run-time.

That said, we have seen many cases where CLR provides substantial performance gains, so long as the processing gain per row or per parameter or per byte substantially exceeds the calling overhead. However, if it is just generally applied in a blanket fashion, you are more likely to see performance decreases than increases (over well-written SQL, it's easy to beat poorly written 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]

Ted Krueger Ten Centuries Points: 1171 More actions · Answer 3

Recent blogs spawned several discussions on this topic that may interest you

http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/split-string-in-sql-server-2005-clr-vs-t

Adam Machanic follows up here then with

http://sqlblog.com/blogs/adam_machanic/archive/2009/04/26/faster-more-scalable-sqlclr-string-splitting.aspx

Recommend reading the comments closely. Several of our peers responded to these blog posts and the topic surrounding memory and other resources with CLR vs T-SQL. Really good reading...

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

CLR is a great feature for things like string splitting. Anyway, there are quiet rare reasons to work with CLR in SQL Server since the integration is not better than today (includes SSE2k8).

Ted Krueger Ten Centuries Points: 1171 More actions · Answer 5

A few of my favorites as a DBA doing file tasks that don't need to go all the way to SSIS

public static void WriteToFile(String content, String filename)

{

try

{

File.WriteAllText(filename, content);

}

catch (Exception ex)

{

SqlContext.Pipe.Send("Error writing to file : " + ex.Message);

}

And

public static void MoveFile(string sfilename, string dfilename)

{

try

{

File.Move(sfilename, dfilename);

}

catch (Exception ex)

{

SqlContext.Pipe.Send("Error writing to file : " + ex.Message);

}

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

Yep. File management is also a nice approach done by CLR. Another one is all those OLE-Automation things instead of sp_OACreate. Hube binary conversions or network conversations like http-requests.

There are some good reasons for using CLR but it's important to keep in mind that usual DML work should be done in TSQL.

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

Florian Reischl (5/15/2009)
Yep. File management is also a nice approach done by CLR. Another one is all those OLE-Automation things instead of sp_OACreate. Hube binary conversions or network conversations like http-requests.
There are some good reasons for using CLR but it's important to keep in mind that usual DML work should be done in TSQL.

It is also good to keep in mind that application work is better suited for a middle/application tier than the database tier as well. If a windows service can do the same work external to SQL Server and use SqlDependencies or the Service Broker External Activator to trigger it's activation, then that is a much better/more scalable model of development since it can be scaled across multiple app servers which are lower costing that conventional SQL Servers.