Is there any justification for really using SQL CLR

Question

This topic has been reported for inappropriate content

Post reply

Is there any justification for really using SQL CLR

SQL-Student

SSC Eights!

Points: 868
More actions
January 20, 2010 at 6:38 am

#218393

I am doing a presentation for all the features of SQL Server, and one of topics is SQL CLR.
The issue is that there is no point which can actually justify its use. Here are some points and counter points.
1. If the logic of an SP is very computationally intensive then use SQL CLR instead of T-SQL
couter argument: Well in this case use a business object which uses .NET to implement the logic. Most DBAs don't know .NET and won't be able to maintain the code anyway. since its .NET code, it is better hosted in a middle tier.
also, there is no data from Microsoft which says that there may be performance improvements if the logic was implement as a SQLCLR SP rather than a normal C# business object.
2. SQLCLR can be used for writing complex triggers etc.
counter point. Comlpex triggers can kill the performance of the database application. it will be better to define a queue, and process the changes asynchronously.
can you please let me know when it is actually sensible to use SQL CLR.

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

CLR is much faster with string manipulations. there are some things that cannot be done in TSQL

Regular expressions is one of the most common example, where it cannot be done natively, and is also much faster for doing string manipulations that could be done in TSQL anyway.

there is a great performance measure made by some of the top posters that compared TSQL to CLR performance wise, including Tally table measures, that showed a good example of a situation where CLR was faster; i

batches of nested find and replace are also faster. REPLACE(REPLACE(REPLACE(....kind of stuff.

also, there is no data from Microsoft which says that there may be performance improvements if the logic was implement as a SQLCLR SP rather than a normal C# business object.

also, just because Microsoft doesn't paint themselves in a corner by saying something is faster or not, does not make it false...they just don't say it because it may not cover every situation. "CLR is faster" is not true all the time.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLPirate SSCrazy Points: 2880 More actions · Answer 2

In our environment I ended up writing a CLR Proc to workaround the fact that xp_fixeddrives does not properly display space information for mounted volumes. Now we have something like an enhanced xp_fixeddrives that gives us the drive letter, the mounted volume paths, volume labels, volume capacity, and volume free space.

Earlier we had used xp_cmdshell (enabling it, getting the information, and then disabling it) for access to the information, but that was always considered a temporary solution.

My .NET knowledge is growing, but I'm not a dev and neither are the other DBAs on my team. If this were a more complicated process that required maintenance, then we probably would have had another group create something. However, the requirements for the CLR proc are minimal, don't change, and are simple enough that the limited .NET knowledge possessed by other members of the team would be sufficient enough to troubleshoot in the off-chance that there's a problem. In this case, using CLR was fast and allowed us to work around a limitation that exists in SQL Server 2005 and 2008.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

Lowell (1/20/2010)
CLR is much faster with string manipulations. there are some things that cannot be done in TSQL
Regular expressions is one of the most common example, where it cannot be done natively, and is also much faster for doing string manipulations that could be done in TSQL anyway.
there is a great performance measure made by some of the top posters that compared TSQL to CLR performance wise, including Tally table measures, that showed a good example of a situation where CLR was faster; i
batches of nested find and replace are also faster. REPLACE(REPLACE(REPLACE(....kind of stuff.
also, there is no data from Microsoft which says that there may be performance improvements if the logic was implement as a SQLCLR SP rather than a normal C# business object.
also, just because Microsoft doesn't paint themselves in a corner by saying something is faster or not, does not make it false...they just don't say it because it may not cover every situation. "CLR is faster" is not true all the time.

Not all string manipulations are faster with CLR's. Not all Regex is faster than T-SQL. How do I know those things? I'm one of the folks you're talking about and Matt Miller and I had great fun testing these things. Unfortunately, both he and I have lost track of most of those tests and haven't been able to find them on this fine forum again.

What I HAVE found is that some people use CLR's to make up for their lack of knowledge of how to do something in T-SQL. I've also found that some of the "Ninjas" on this forum do use them correctly for things like file handling and the like.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Shredding XML is hands down faster with SQLCLR and becoming more and more common in database work. Binary Serialization and deserialization of data with Service Broker is another great use for SQLCLR. A lot of people use XML for their SB messages which is bloated with definition attributes and elements. Passing a binary serialization of the message is faster because it is smaller.

I am not a fan of most CLR implementations I see people doing, I think there is usually a better way to handle certain processes, but if a process requires loop processing, CLR can be better at that kind of work. Whether or not it is considered to be Business Layer Logic or not depends a lot on the kind of business you are in and what may or may not be consuming the data. If a dozen applications need a complex calculation using CLR in SQL to provide that versus placing it middle tier makes a little more sense, the code is only in one place and you have a simplified deployment model there.

Smaller shops may accept SQLCLR for coding simplicity where TSQL has a slight advantage performance wise and they don't have a Jeff Moden or Sr. DBA that knows how to write TSQL to solve the problem. If the difference in perf is only a few hundred milliseconds, most people won't even care, which is a sad truth.

Pedro DeRose [MSFT] Old Hand Points: 307 More actions · Answer 5

Here are a couple of key use scenarios for SQL CLR:

1. Complex computation that processes lots of data: before SQL CLR, when you had complex computation over substantial data, you had to choose between two evils. The first is writing your logic in .NET, keeping it on the mid-tier, and shipping your data. But with substantial data, the cost of moving it around is prohibitive. The second option is to write your logic in T-SQL to keep it near the data. However, if your logic is complex, writing it in T-SQL can be difficult, awkward, or even impossible, and is a productivity hit for many developers (they don't know T-SQL, don't have access to sophisticated structures and libraries, etc.). SQL CLR lets you have your cake and eat it too: you can write the complex logic in .NET, and the logic can stay near the data.

2. Streaming results of TVF: T-SQL table-valued functions materialize their results in temp tables, whereas CLR TVFs can stream them.

3. Easier access to external data sources: much simpler in .NET than T-SQL.

4. Rich types: when you need an atomic type for a column, but also need it to have rich functionality (e.g., methods). For instance, look at hierarchyid and the spatial data type. Both of these were implemented with CLR.

For a more in-depth discussion, including a break-down of when to use CLR and when to not, check out this article: http://msdn.microsoft.com/en-us/library/ms345136%28SQL.90%29.aspx

Hope that's of some help.

Pedro DeRose
Program Manager, Microsoft SQL Server

(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Pedro DeRose [MSFT] (1/21/2010)
3. Easier access to external data sources: much simpler in .NET than T-SQL.

Funny you should mention that because there is no SAFE or EXTERNAL ACCESS support for accessing external data sources such as Oracle for SQLCLR so you have to do things like use UNSAFE and enable TRUSTWORTHY on the database to load third party assemblies that you don't have key files for or create excessive amounts of work trying to build certificates off all the dependent signed assemblies. This isn't acceptable in some environments. Yesterday the connect feedback request to make the OracleClient SAFE was closed because the .NET folks decided to deprecate it from the product in liu of ODP.NET from Oracle. I wouldn't list this as a selling point for SQLCLR.

Pedro DeRose [MSFT] Old Hand Points: 307 More actions · Answer 7

Jonathan Kehayias (1/21/2010)
Funny you should mention that because there is no SAFE or EXTERNAL ACCESS support for accessing external data sources such as Oracle for SQLCLR so you have to do things like use UNSAFE and enable TRUSTWORTHY on the database to load third party assemblies that you don't have key files for or create excessive amounts of work trying to build certificates off all the dependent signed assemblies.

You're right: some external access requires jumping through security hoops, which makes it painful. Striking a good balance beween security and ease of use is still a work in progress.

That said, EXTERNAL ACCESS often works fine (e.g., for files, networks, registry settings). And even when you have to jump through security hoops, the actual code that does the data access will generally be simpler and clearer in .NET than T-SQL. Much as I like T-SQL, it just wasn't built for that sort of thing. 🙂

Pedro DeRose
Program Manager, Microsoft SQL Server

(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Pedro DeRose [MSFT] (1/21/2010)
Here are a couple of key use scenarios for SQL CLR:
...
2. Streaming results of TVF: T-SQL table-valued functions materialize their results in temp tables, whereas CLR TVFs can stream them.
...

Really? I have definitely been under the impression, that although it is possible to stream data from CLR to T-SQL, that this is not the case either when using the context-connection to return the data or when trying to pass data through the return-table of a CLR TVF.

If I am wrong about this (and believe me, I would love to be). then please provide a VB example that I can try, because I will use that sucker ALL the time.

[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]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

Barry,

I beleive there is an example on Adam Machanic's blog post :

http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 10

Pedro DeRose [MSFT] (1/21/2010)
Here are a couple of key use scenarios for SQL CLR:
1. Complex computation that processes lots of data: before SQL CLR, when you had complex computation over substantial data, you had to choose between two evils. The first is writing your logic in .NET, keeping it on the mid-tier, and shipping your data. But with substantial data, the cost of moving it around is prohibitive. The second option is to write your logic in T-SQL to keep it near the data. However, if your logic is complex, writing it in T-SQL can be difficult, awkward, or even impossible, and is a productivity hit for many developers (they don't know T-SQL, don't have access to sophisticated structures and libraries, etc.). SQL CLR lets you have your cake and eat it too: you can write the complex logic in .NET, and the logic can stay near the data.

Although I very much agree with most of that, the "Catch 22" there is that most T-SQL users don't know what a "complex computation" actually is nor how easy most of them can be solved in T-SQL even "over a substantial data" in a very high performance manner. My recommendation to most folks is to stop justifying the use of CLR's (and cursors) just because they don't know how to do something. If someone has to work with T-SQL, then it's part of their job and they should spend a more time learning how to do their job correctly. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

Jonathan Kehayias (1/21/2010)
Barry,
I beleive there is an example on Adam Machanic's blog post :
http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

I'm curious how we go about proving one way or another. It's fairly fast, but it sure seems to be acting like it's waiting for everything to be built before streaming. I threw a 70MB snippet at it, and it damn near crashed my workstation (with 8GB ram).

I am a bit tired tonight so I will have to review what I was doing before fully crying foul. Still - this might need a second look.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Adam Machanic SSCoach Points: 15370 More actions · Answer 12

Jeff Moden (1/21/2010)
Although I very much agree with most of that, the "Catch 22" there is that most T-SQL users don't know what a "complex computation" actually is nor how easy most of them can be solved in T-SQL even "over a substantial data" in a very high performance manner. My recommendation to most folks is to stop justifying the use of CLR's (and cursors) just because they don't know how to do something. If someone has to work with T-SQL, then it's part of their job and they should spend a more time learning how to do their job correctly. 😉

So what you're saying is that most users--with the exception of you--are too stupid to be doing their job, aren't up to speed with their technology, and are too lazy to learn how to do things properly? Wow! You must feel really proud of yourself for bucking the trend. Great job.

The fact that you refer to assemblies as "CLR's [sic]" speaks volumes about your actual level of expertise.

:rolleyes:

--
Adam Machanic
whoisactive

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Adam Machanic (1/22/2010)
Jeff Moden (1/21/2010)
Although I very much agree with most of that, the "Catch 22" there is that most T-SQL users don't know what a "complex computation" actually is nor how easy most of them can be solved in T-SQL even "over a substantial data" in a very high performance manner. My recommendation to most folks is to stop justifying the use of CLR's (and cursors) just because they don't know how to do something. If someone has to work with T-SQL, then it's part of their job and they should spend a more time learning how to do their job correctly. 😉
So what you're saying is that most users--with the exception of you--are too stupid to be doing their job, aren't up to speed with their technology, and are too lazy to learn how to do things properly? Wow! You must feel really proud of yourself for bucking the trend. Great job.
The fact that you refer to assemblies as "CLR's [sic]" speaks volumes about your actual level of expertise.
:rolleyes:

I'm sorry, but just because Steve is on vacation is no reason for professionalism to go out the window. Please refrain from personal attacks. I read Jeff Moden's comments and they were made in a general manner and did not attack anyone directly.

I have learned much from Jeff and others on SSC, and I personally feel that this directed comment was totally unprofessional and uncalled for in this public forum.

Adam Machanic SSCoach Points: 15370 More actions · Answer 14

Lynn Pettis (1/22/2010)
I'm sorry, but just because Steve is on vacation is no reason for professionalism to go out the window. Please refrain from personal attacks. I read Jeff Moden's comments and they were made in a general manner and did not attack anyone directly.

Lynn,

I had no idea that Steve was on vacation, but after reflecting I agree with your general sentiment: I went a bit too far. The FUD around SQLCLR is thick and it's frustrating for me, especially when someone is implying that people who use the technology are morons. I happen to be a heavy SQLCLR user and on some level took Jeff's general statements personally. I suppose the ad hominem counter was not necessary but I have to say that I still find it odd that someone so interested in stomping out this technology can't be bothered to use the correct terminology.

--
Adam Machanic
whoisactive