Actual Reason to Use CLR

  • Steve created a forum, might as well use it...

    The one question that none of the DBA's where I work have been able to answer is, why do we need CLR? What's it going to do for us that we couldn't do with TSQL or out in the application code? Has anyone come up with some good answers to this question?

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • i think the purpose for the clr is to blur the divide between the database and the application program.


    Everything you can imagine is real.

  • Grant,

    Microsoft is just joining the party because Oracle have been running Java runtime in memory since oracle 8i or 9i and a .NET runtime albeit out of process is in development so Microsoft needed to add the .NET runtime in SQL Server.  Another thing with LINQ coming in .NET 3.5, like XML before it what is a query could be anything.

     

    Kind regards,
    Gift Peddie

  • CLR is there to allow you to do what you can not do in T-SQL. You should always prefer T-SQL over CLR when you can because it performs better.

    Perhaps in the near future, the CLR implementation will be more refined, but until then, I suggest using it only when you must.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • I agree with Mr. or Mrs 500 you should always use TSQL when doing data manipulation using DML, you should consider CLR functions when ur not doing DML, but rather thing like string concatenation or string manipulations.

  • There is ofcourse the classic literature :

    Using CLR Integration in SQL Server 2005 (http://msdn2.microsoft.com/en-us/library/ms345136.aspx)

    http://www.programmersheaven.com/2/SQL-server-2005-school-lesson-6

    Managed Code (CLR)

    You should use managed code for creating database objects in the following situations:

    ü      You require complex programmatic constructs or features such as object orientation to complete a task.

    ü      You need to use the .NET Framework’s base class library to perform a task that is difficult or impossible with Transact-SQL.

    ü           Your required functionality will be CPU-intensive.

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Yeah, I've read a lot of that stuff. I was just wondering if anyone had come up with a compelling, real-world, example of using CLR and getting more or better functionality out of it that works better/faster/stronger than simply running the same code on an application server or a client.

    I have one, very small, example. Itzik Ben-Gan shows how to use CLR & common expressions available in .NET languages to clean all the parameters off of stored procedure calls in a trace file. It's nice, but it's hardly a good reason to run and spend time honing C# skills.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • I have used the CLR and extended stored procedures for a couple of reasons.  They both are "Transaction related".  I have code that allows me to log SQL execution without being affected by a rollback.  The other is to do processing that was in a transaction where I needed to process external data while still having the ability to rollback the transaction if there was an error while doing so.

  • As Itzik also pointed out the CLR comes in handy if for calculations; things that have nothing to do with database access, but nevertheless need to be done in the db and not on the client. Probably we will not benefit from this in stored procedures and triggers, but I can imagine it in UDFs and maybe there exists a business case for CLR datatypes.

    We are using a financial database with check digits for standard codes like ISIN and SEDOL. We need to implement check constraints that enforce them. I have implemented them both in TSQL and vb.net. I tested the checks in a loop and here we see a significant improvement: depending on the algorithm, the .net version runs twice to three times as fast.

    AlgorithmtimesCLRTSQLPerc

    Sedol1000468057,50

    1000037681046,42

    1000003726816045,66

    Isin10006015638,46

    10000534182629,24

    10000053801638032,84

    Of course for all data access I would certainly use TSQL.

    The code: (the explanation of the algorithms can be found at Wikipedia.org)

    go

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author:Jan DV

    -- Create date: 8/05/2007

    -- Description:Check validity of an SEDOL code

    -- Examples: 6870401, B06G5D9, 5330047

    --dbo.fn_IIM_IsValidSedol('B06G5D9') = 1

    --dbo.fn_IIM_IsValidSedol('6870401') = 1

    -- =============================================

    IF EXISTS(SELECT * FROM sys.objects WHERE name = 'fn_IIM_IsValidSedol' AND type = 'FN')

    DROP FUNCTION dbo.fn_IIM_IsValidSedol

    GO

    CREATE FUNCTION fn_IIM_IsValidSedol

    (

    -- ISIN code to be verified

    @sSedol char(7)

    )

    RETURNS int

    AS

    BEGIN

    -- weight factor: 131739

    DECLARE @sArrWeighting char(6)

    DECLARE @npos smallint

    DECLARE @n smallint

    DECLARE @nSum int

    DECLARE @nCheckDigit smallint

    DECLARE @nReturn int

    -- check if datalength = 7

    IF IsNumeric(RIGHT(@sSedol,1)) = 1

    SELECT @nCheckDigit = CONVERT(smallint,RIGHT(@sSedol,1))

    ELSE

    SELECT @nCheckDigit = -1

    SELECT @sSedol = UPPER(@sSedol), @npos = 1, @nSum = 0

    SELECT @sArrWeighting = '131739'

    WHILE @npos < 7

    BEGIN

    SELECT @n =

    CASE WHEN ISNUMERIC(SUBSTRING(@sSedol,@nPos,1)) = 1

    THEN CONVERT(smallint,SUBSTRING(@sSedol,@nPos,1))

    ELSE ASCII(SUBSTRING(@sSedol,@nPos,1)) - 55

    END

    SELECT @nSum = @nSum + (@n * CONVERT(int,SUBSTRING(@sArrWeighting,@nPos,1)))

    SELECT @npos = @npos + 1

    END

    -- return if checkdigit is correct or not

    IF ((10 - (@nSum % 10)) = @nCheckDigit) SELECT @nReturn = 1 ELSE SELECT @nReturn = 0

    RETURN @nReturn

    END

    go

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author:Jan DV

    -- Create date: 23/04/2007

    -- Description:Check validity of an ISIN code

    -- Examples: US0378331005, AU0000XVGZA3

    --dbo.fn_IIM_IsValidIsin('AU0000XVGZA3') = 1

    --dbo.fn_IIM_IsValidIsin('AU0000XVGZA3') = 1

    -- =============================================

    IF EXISTS(SELECT * FROM sys.objects WHERE name = 'fn_IIM_IsValidIsin' AND type = 'FN')

    DROP FUNCTION dbo.fn_IIM_IsValidIsin

    GO

    CREATE FUNCTION fn_IIM_IsValidIsin

    (

    -- ISIN code to be verified

    @sIsin char(12)

    )

    RETURNS int

    AS

    BEGIN

    DECLARE @nCheckDigit smallint

    DECLARE @nCounter smallint

    DECLARE @nASCII smallint

    DECLARE @sArrNumbers varchar(24)

    DECLARE @sChar char(1)

    DECLARE @nEven smallint

    DECLARE @nTotal int

    SELECT @nCheckDigit = CONVERT(smallint,RIGHT(@sIsin,1))

    -- convert chars to numeric, with an offset of 10

    SELECT @sIsin = Upper(@sIsin), @nCounter = 1, @sArrNumbers = ''

    WHILE @nCounter 64 AND @nASCII < 91

    SELECT @sArrNumbers = @sArrNumbers + CONVERT(varchar(2), @nASCII - 55) -- offset 10

    ELSE

    SELECT @sArrNumbers = @sArrNumbers + @sChar

    SELECT @nCounter = @nCounter + 1

    END

    -- if odd, then double the first numbers; else 2nd list.

    SELECT @nEven = 1 - (DATALENGTH(@sArrNumbers) % 2)

    SELECT @nCounter = 1, @nTotal = 0

    WHILE @nCounter <= DATALENGTH(@sArrNumbers)

    BEGIN

    -- if a number has 2 digits, sum them seperately. Multiply the correct column. @nEven is used to shift the multiplication position.

    SELECT @nTotal = @nTotal +

    (((CONVERT(int, SUBSTRING(@sArrNumbers, @nCounter, 1)) * (((@nCounter + @nEven) % 2) + 1)) % 10) +

    ROUND(CONVERT(int, SUBSTRING(@sArrNumbers, @nCounter, 1)) * (((@nCounter + @nEven) % 2) + 1) / 10,0))

    SELECT @nCounter = @nCounter + 1

    END

    -- return if checkdigit is correct or not

    IF ((10 - (@nTotal % 10)) = @nCheckDigit) SELECT @nTotal = 1 ELSE SELECT @nTotal = 0

    RETURN @nTotal

    END

    The CLR code:

    Imports System

    Imports System.Data

    Imports System.Data.SqlClient

    Imports System.Data.SqlTypes

    Imports Microsoft.SqlServer.Server

    Partial Public Class UserDefinedFunctions

    _

    Public Shared Function fn_IIM_CLRCheckSedol(ByVal sSedol As SqlString) As SqlBoolean

    Dim arWeighting() As Byte = {1, 3, 1, 7, 3, 9}

    Dim arSedol(5) As Byte

    Dim nCheckDigit As Byte

    Dim nTotal As Byte = 0

    Try

    If IsNumeric(sSedol.ToString) And Len(sSedol.ToString) = 7 Then

    Dim n As Byte

    For n = 1 To 6

    arSedol(n - 1) = CByte(Mid(sSedol.ToString, n, 1))

    Next

    nCheckDigit = CByte(Mid(sSedol.ToString, 7, 1))

    ' Sum the result

    For n = 0 To 5

    nTotal += (arSedol(n) * arWeighting(n))

    Next

    'Return true if there is a match with the checkdigit

    Return (10 - (nTotal Mod 10)) = nCheckDigit

    Else : Return False 'Sedol passed should be numerical

    End If

    Catch

    Return False

    End Try

    End Function

    End Class

    Imports System

    Imports System.Data

    Imports System.Data.SqlClient

    Imports System.Data.SqlTypes

    Imports Microsoft.SqlServer.Server

    Partial Public Class UserDefinedFunctions

    _

    Public Shared Function fn_IIM_CLRCheckISIN(ByVal spIsin As SqlString) As SqlBoolean

    If Len(spIsin.ToString) = 12 Then

    Dim arrNumbers(22) As Integer 'ISIN is 12 char, 22 is the max size the array can become because there is one checkdigit

    Dim sIsin As String

    Dim nPos As Integer = 1

    Dim nArrPos As Integer = 0

    Dim sChar As String

    Dim nValue As Integer = 0

    Dim nOdd As Integer = 0

    Dim nCheckDigit As Integer

    Try

    'Make sure that all ascii values are in upper range

    sIsin = spIsin.ToString.ToUpper

    nCheckDigit = CInt(Mid(sIsin, 12, 1))

    For nPos = 1 To 11

    sChar = Mid(sIsin, nPos, 1)

    If IsNumeric(sChar) Then

    arrNumbers(nArrPos) = CInt(sChar)

    nArrPos += 1

    Else

    nValue = Asc(sChar) - 55

    If nValue > 9 Then

    arrNumbers(nArrPos) = (nValue \ 10)

    arrNumbers(nArrPos + 1) = (nValue Mod 10)

    nArrPos += 2

    Else

    arrNumbers(nArrPos) = nValue

    nArrPos += 1

    End If

    End If

    Next

    'If # elements are odd, multiply odd values by 2 else even values.

    nOdd = (nArrPos Mod 2)

    For nPos = 0 To (nArrPos - 1)

    arrNumbers(nPos) = arrNumbers(nPos) * (((nPos + nOdd) Mod 2) + 1)

    Next

    ' Add up the individual digits

    nValue = 0

    For nPos = 0 To nArrPos - 1

    If arrNumbers(nPos) < 10 Then

    nValue += arrNumbers(nPos)

    Else

    nValue += (arrNumbers(nPos) \ 10)

    nValue += (arrNumbers(nPos) Mod 10)

    End If

    Next

    'Return True if checkdigit matches

    Return (10 - (nValue Mod 10) = nCheckDigit)

    Catch

    Return False

    End Try

    Else

    Return False 'Lenghth of ISIN code is not 12

    End If

    End Function

    End Class

    And the test:

    DECLARE @dStart datetime

    DECLARE @dStop datetime

    DECLARE @n int

    DECLARE @bResult bit

    SELECT @dStart = Getdate()

    SELECT @n=0

    WHILE @n < 100000

    BEGIN

    SELECT @bResult= dbo.fn_IIM_CLRCheckSedol('0263494')

    SELECT @bResult= dbo.fn_IIM_CLRCheckSedol('A263494')

    SELECT @n = @n + 1

    END

    SELECT @dStop = getdate()

    SELECT datediff(ms, @dStart, @dStop)-- 46 ms in CLR version

    --- Test ISIN

    DECLARE @dStart datetime

    DECLARE @dStop datetime

    DECLARE @n int

    DECLARE @bResult bit

    SELECT @dStart = Getdate()

    SELECT @n=0

    WHILE @n < 100000

    BEGIN

    SELECT @bResult= dbo.fn_IIM_IsValidIsin('BE0003788057')

    SELECT @bResult= dbo.fn_IIM_IsValidIsin('AU0000XVGZA3')

    SELECT @n = @n + 1

    END

    SELECT @dStop = getdate()

    SELECT datediff(ms, @dStart, @dStop)

  • The lead developer at my former company had downloaded some canned code off of some website that used the CLR to access the Windows API for time zone information which gave us nice little functions that converted customer's time zones to our local time and back.

    They were nice little functions that slowed down the entire system because every time someone converted a time, they had to access the CLR which in turn accessed the Windows API. It also caused a memory leak when the system was under a heavy load that would silently kill connections and then eventually restart the SQL Service.

    I replaced it with a process that imports the same time zone info using xp_instance_reg_read once a day at 4 AM and then uses the data in the TimeZones table to make the same conversions without slowing anything down and without causing a memory leak.

    There's one real life example where the CLR should not have been used.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Robert,

    My functions are not used in production yet, so I wouldn't really know about this behaviour. Thanks for the warning, I will keep an eye on memory leaks and overall performance and keep the TSQL version standby.

    Real life experience is important for technology like this!

    thanks,

    Jan

  • Very interesting stuff. Thanks for sharing. I'd love to know if you do find any kind of memory leak since, from a performance stand point you already proved that it was faster.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • I also found it handy to write a CLR procedure to a SQL Broker Queue process.  The CLR procedure formatted an XML document based on parameters passed in message body, and FTP the document.  It was much easier created the XML document and using the .Net framework for FTP client processing than to try to do the same in TSQL.



    Mark

  • another reason is code reuse.

    In our org, we use CLR functions to expose to our procs in SQL a library of useful functions that are shared with other managed components.  For example, in our system we need to be able to convert from UTC to local time correctly for every county in the US, as well as every country in Europe.  This is non-trivial because DST rules change in both time and space.  I won't go into it except to say that Indiana is truly a hellish state as far as telling the time is concerned!

    It was very convenient to put this code on one class, and then expose it both to other managed components as well as SQL through CLR udfs.

     

    ---------------------------------------
    elsasoft.org

  • This sounds a lot like the CLR code that was killing our system. If you want, I can send you a solution in nothing but TSQL.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

Viewing 15 posts - 1 through 15 (of 72 total)

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