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 12345»»»

Is there any justification for really using SQL CLR Expand / Collapse
Author
Message
Posted Wednesday, January 20, 2010 6:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 12:40 PM
Points: 20, Visits: 124
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.
Post #850429
Posted Wednesday, January 20, 2010 7:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 12,965, Visits: 32,525
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #850486
Posted Wednesday, January 20, 2010 6:03 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 10:25 AM
Points: 41, Visits: 353
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.


------------------------------------
Rule #1: Replication sucks
Rule #2: When in doubt, reboot.
Rule #3: F.Y.S.
Post #850935
Posted Wednesday, January 20, 2010 9:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 35,792, Visits: 32,473
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #850980
Posted Thursday, January 21, 2010 6:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #851201
Posted Thursday, January 21, 2010 3:05 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 28, 2010 9:54 AM
Points: 9, Visits: 53
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.)
Post #851596
Posted Thursday, January 21, 2010 3:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #851605
Posted Thursday, January 21, 2010 3:52 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 28, 2010 9:54 AM
Points: 9, Visits: 53
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.)
Post #851617
Posted Thursday, January 21, 2010 5:00 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #851651
Posted Thursday, January 21, 2010 5:31 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #851659
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse