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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
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)))
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
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 71 total)
You must be logged in to reply to this topic. Login to reply