Simple SQL CLR Integration
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aAnajwala/simplesqlclrintegration.asp
-
I still haven't had the chance to use SQL2005 yet but this seems like quite a slow and painful way to do something that could be done so much easier in T-SQL, any reason on why you would go to all this trouble? Until you get down to the level of calling COM objects from your T-SQL I can't see any benefit from using C# or .Net to write SPs.
Anyone agree / disagree?
-
I'm sure that CLR will be usefull for situations where TSQL isn't quite up to the job.
Speaking personally, if a developer (with a lot of assistance) can't write something in TSQL and can prove that there's a proper reason for using CLR then i might be open to it, otherwise I just don't want to introduce it onto our systems.
Not everyone is good at writting optimised code (I'm being nice
) and allowing them to create their own stuff is pretty much saying 'here you go, add a bottleneck that we wont be able to find later'. -
String-manipulation intensive operations are a prime example where the CLR will most likely kick T-SQL's rear end. I'm currently in the process of running some tests on the SQL 2K5 Express June CTP that perform millions of string manipulations in T-SQL as well as in a CLR UDF. So far T-SQL string manipulations can't even compare to a .NET StringBuilder in a UDF.
Another thing is bit-level manipulations. Ever try to encrypt or encode a string of bits using T-SQL? If you actually succeed, it will bring your SQL Server to a crawl. The CLR can handle extensive bit level manipulations much better than T-SQL.
Finally, there is a lot of O/S-level functionality you might want to call from within a T-SQL script. You might want to call the Windows Crypto API, or call the O/S and ask how much free disk space you have. COM is overkill for these type tasks.
That's the problem with oversimplified examples, like "Hello World". If all you're doing is returning the string "Hello World" you'd be better off with T-SQL. If you're trying to do something that requires a lot of string manipulation, excessive looping, Windows API calls, extensive bit-level manipulations, etc., you'd do well to look at UDF's.
-
Take a look at this article on Double Metaphone in T-SQL:
http://www.windowsitpro.com/Article/ArticleID/26094/26094.html?Ad=1
The author provides a T-SQL version of the source code. In my preliminary tests, my port of Double Metaphone to the SQL 2K5 CLR runs over 8 times faster than this T-SQL Code.
For me, getting 8 hours worth of T-SQL processing done in 1 hour is a pretty good and "proper reason for using CLR."
-
StringBuilder is a great example. So is pretty much any math operation. Recently, I've been playing with the following method, which mathematically reverses an integer:
-----------------------------------------------------------
[SqlFunction]
public static int ReverseNumber (int number)
{
int reverse = 0;
do
{
reverse = (reverse * 10) + (number % 10);
number = number / 10;
}
while (number > 0);
return (reverse);
}
-----------------------------------------------------------
Here is the equivalent in a T-SQL UDF:
-----------------------------------------------------------
CREATE FUNCTION dbo.NumberReverse
(
@InputNum INT
)
RETURNS INT
AS
BEGIN
DECLARE @Reverse INT
SET @Reverse = @InputNum % 10
SET @InputNum = @InputNum / 10
WHILE @InputNum > 0
BEGIN
SET @Reverse = (@Reverse * 10) + (@InputNum % 10)
SET @InputNum = @InputNum / 10
END
RETURN (@Reverse)
END
GO
-----------------------------------------------------------
Run both of these 10000 or 100000 times on your system, using a script such as the following:
DECLARE @i INT
SET @i = 1
DECLARE @j-2 INT
SET @j-2 = 1234567
WHILE @i < 10000
BEGIN
SET @j-2 = dbo.NumberReverse(@j)
SET @i = @i + 1
END
... On my test system, the CLR version runs twice as fast. I'd be interested in hearing others' results.
--
Adam Machanic
whoisactive -
This is the sort of thing that would be useful to know about SQL2005, after reading the previous article as a DBA you think ok great I can write code in CLR, a simple PRINT and a SELECT now takes 10 times as much code and is far more complex whats the big deal.
Possibly an article letting us know the benefits of using CLR over T-SQL might be worthwhile, but again depending on the type of work you're doing on your system excluding hardcore string manipulation and calling COM objects are there any other benefits to using the CLR over T-SQL?
-
COM objects? Please, don't do that if you can avoid it. COM is unmanaged and unsafe.
Being able to call into .NET assemblies (and the .NET BCL), on the other hand, is one of the main benefits.
--
Adam Machanic
whoisactive -
Thats fair enough, I wouldn't use COM objects very often normally anyway, usually just FSO for pumping out the odd file here and there. So anyway on a general day to day basis how often would you use CLR compared to T-SQL? I can see the definatly see certain circumstances when the CLR would prove very useful but probably only accounting for 5% of the day to day work for most DBAs. I'm not against using it I just haven't had the chance to use it and am wondering how useful it really is.
-
Curious about the CREATE ASSEMBLY command - does it just create a pointer, or does it actually compile the code into the SQL server? I'm trying to imagine the scenario of when you need to edit the CLR method - do you then need to drop the current assembly, and then rebuild the assembly and internal functions etc? Seems like a pain. If the CREATE ASSEMBLY just pointed to the dll then it seems like it would be easy.
-
Not a whole lot. I think 5% is a very accurate figure for the current implementation. However, if MS changes a few things about the way UDTs and UDAs work in the next version, I think that number could jump quite a bit.
I also predict that in a future version MS will implement a fully object-based data access library that can compete with TSQL from a performance point of view. Note, I have no inside information to back that up, it's just what I think I see based on some of the direction I see all of this going in. If that does turn out to be true, I think we'll see the landscape drastically change as developers really will no longer have a huge incentive to learn T-SQL. But that's at least a few years off.
--
Adam Machanic
whoisactive -
The assembly is loaded into the database. The DLL is no longer used -- and can even be deleted.
You are correct, there can be many issues, and it is not an easy task. Not so bad for functions and stored procedures most of the time, but quite interesting for user-defined types...
--
Adam Machanic
whoisactive -
This is the first I've been able to stop and look at this. Is it me, or is this CLR integration a lot like our current extended stored procedure capability?
Steve Miller -
Yep that sounds about right to me, except it seems a bit simpler to use. Anyway I'm convinced of its benefits, not something I'd use that often but when it's used it seems like it would be of great use.
-
Similar, perhaps, with several key differences:
1) Managed code vs. unmanaged code -- means that CLR routines are safer than XPs.
2) XPs are created per server. CLR routines are created per database.
3) XPs are only "procedures". CLR routines can take the form of procedures, functions, types, aggregates, or triggers.
4) In-process ADO.NET context connection for CLR routines vs. "bound" connections for XPs -- means less overhead for CLR routines (no need to create a new connection).
--
Adam Machanic
whoisactive
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply