CLR Funtions

  • manoj2001

    SSCrazy

    Points: 2175

    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

  • Tom257

    SSChampion

    Points: 11475

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

    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

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

    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

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

    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

    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.

    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]

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

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