Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Simple SQL CLR Integration Expand / Collapse
Author
Message
Posted Tuesday, August 9, 2005 5:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 5:30 AM
Points: 1, Visits: 23
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aAnajwala/simplesqlclrintegration.asp
Post #208912
Posted Thursday, September 1, 2005 2:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, September 15, 2014 6:15 PM
Points: 480, Visits: 458

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?




Post #216017
Posted Thursday, September 1, 2005 2:57 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:04 AM
Points: 585, Visits: 53

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'.

 

Post #216023
Posted Thursday, September 1, 2005 8:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:20 PM
Points: 1,276, Visits: 1,134
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.
Post #216138
Posted Thursday, September 1, 2005 9:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:20 PM
Points: 1,276, Visits: 1,134
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."
Post #216148
Posted Thursday, September 1, 2005 9:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:37 AM
Points: 1,113, Visits: 703
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 INT
SET @j = 1234567
WHILE @i < 10000
BEGIN
SET @j = 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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #216152
Posted Thursday, September 1, 2005 9:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, September 15, 2014 6:15 PM
Points: 480, Visits: 458

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?

 




Post #216162
Posted Thursday, September 1, 2005 9:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:37 AM
Points: 1,113, Visits: 703
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #216167
Posted Thursday, September 1, 2005 9:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, September 15, 2014 6:15 PM
Points: 480, Visits: 458
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.


Post #216176
Posted Thursday, September 1, 2005 9:51 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 18, 2007 11:44 AM
Points: 132, Visits: 1

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.

 




Post #216179
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse