This topic has been reported for inappropriate content
January 22, 2010 at 10:33 am
Adam Machanic (1/22/2010)
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,
I'm not going to get into any discussion regarding SQLCLR v T-SQL as I have no experience using SQLCLR. Not that I don't want to use it, just that there has been no reason for me to even explore that option at this time. We support 3rd party apps, so the opportunity isn't really there at this time.
If I had the opportunity to sit down with someone like you and be mentored in its proper use, it would be great.
As for Jeff's opinion regarding SQLCLR, in some ways I agree, use the right tool for the job. SQLCLR may not always be the right tool. And perhaps Jeff is so adamant about NOT using SQLCLR because of past experiences with developers who used SQLCLR inappropriately, or where T-SQL actually was a better choice. Not having walked in his shoes, I can only guess.
I do agree with him in his sentiment about learning how to do things. I have learned much from him over the 4+ years I have been active on SSC, he has truely taught me things I'd never have thought of on my own. Much of what I have learned prior to that was self taught while doing it.
I appreciate your response, it means much to me in a time when many don't feel the need to admit they may have been wrong. It speaks to your professionalism and would welcome the opportunity to meet you sometime.
thanks,
Lynn
January 22, 2010 at 11:09 am
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. 😉
I agree that T-SQL can solve many computational problems, including some which, up until recent versions, were assumed to be too complex. In fact, the article to which I linked earlier explicitly lists several such scenarios, and emphasizes that T-SQL is still the right choice for anything set-based, declarative, etc.
Of course, there are many things for which T-SQL is not well suited (see the above mentioned article for some guidance). It may be possible to do these things in T-SQL, but it's also possible to cut a board with a hammer; it's just messy and likely painful. Think of CLR as introducing a saw into the toolbox. A good builder knows not to use it to drive nails, but that it's the right choice for cutting boards.
Which, I think, may be a good slogan for SQLCLR: better than cutting boards with a hammer. 🙂
(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.)
January 22, 2010 at 11:27 am
Matt Miller (#4) (1/21/2010)
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).
What do you mean when you say that it almost crashed your workstation?
It will take a bit of time for 70MB worth of data to get marshaled into the CLR memory space, but it shouldn't take too long. I would be surprised if the delay would be at all noticeable. And after that the data should stream out. Of course "streaming" behavior depends on how you're testing the thing. If you use SSMS in grid mode the results stream back a lot more slowly than if, e.g., you run them through a SqlDataReader in a simple console app...
--
Adam Machanic
whoisactive
January 22, 2010 at 11:34 am
RBarryYoung (1/21/2010)
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.
It is possible to stream data out of a SQLCLR routine in two different ways:
A) Via a stored procedure and the SendResultsStart/SendResultsEnd pattern
B) Via a TVF
With regard to the context connection, it works fine with the stored procedure, and not so well if you want to stream data through a TVF.
Alas, stored procedures have their own problems: you can't join to them, etc, unless you are willing to do a hack with linked servers and OPENQUERY (Google if you're actually interested in said hack--I don't recommend it), and even simply inserting the results of a stored proc into a temp table has a lot more overhead than doing the same from a TVF.
So I don't recommend stored procedures in this case, and as a result I don't recommend the context connection. The "fix" is to do a loopback (non-context) connection in your TVF, and then everything works quite nicely, although you will have to be extra-extra-extra careful with exception handling to make sure that should a problem occur you don't wind up with orphaned connections (quite easy to demo, unfortunately--the query processor does not always raise an error on the CLR side when an Attention event occurs).
--
Adam Machanic
whoisactive
January 22, 2010 at 12:57 pm
abhishes (1/20/2010)
I am doing a presentation for all the features of SQL Server, and one of topics is SQL CLR.
Intensive byte level operations within large objects are hard or impossible to do efficiently in T-SQL. McLaren use CLR and Filestream features for processing engineering data in SQL Server:
http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000001476
January 22, 2010 at 1:48 pm
David Portas (1/22/2010)
abhishes (1/20/2010)
I am doing a presentation for all the features of SQL Server, and one of topics is SQL CLR.Intensive byte level operations within large objects are hard or impossible to do efficiently in T-SQL. McLaren use CLR and Filestream features for processing engineering data in SQL Server:
http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000001476
That was a really interesting read, just being a gearhead, not to mention the actual technology behind how they do all that. Thanks for sharing.
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[/url]
January 22, 2010 at 1:56 pm
I've had a couple of good uses for CLR.
One is similar to something already mentioned, it returns drive data about remote drives. I use this to test for available drive space while running backups, before running a large database backup. I've found that it's better to get an e-mailed alert saying a drive probably doesn't have room for the backup, over getting an alert that the backup job failed because of a drive being full.
Another access a third-party DLL for address list validation.
Another was a complex binary manipulation (re-dithering jpegs stored in varbinary(max) columns).
For the last two, the advantage of being able to do it on the database server, in CLR, made it MUCH faster and more efficient than pulling the data into a business layer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2010 at 4:39 pm
Adam Machanic (1/22/2010)
Matt Miller (#4) (1/21/2010)
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).What do you mean when you say that it almost crashed your workstation?
It will take a bit of time for 70MB worth of data to get marshaled into the CLR memory space, but it shouldn't take too long. I would be surprised if the delay would be at all noticeable. And after that the data should stream out. Of course "streaming" behavior depends on how you're testing the thing. If you use SSMS in grid mode the results stream back a lot more slowly than if, e.g., you run them through a SqlDataReader in a simple console app...
As in - I essentially lost control of the machine for somewhere in the 5-10 minutes range then it let go and the task was done. TaskMgr showed the memory resources on SSMS pegge right around 250MB, then dropped precipitously. right at the end.
It was just something I had seen with some previous attempts with CLR functions. It just seems to me that most seem to essentially build the entire result set in memory before starting to send it down range. This doesn't seem to happen with the CLR stored procedures where I've used streaming, where big output seems to start happening just about immediately.
I suspect it may be the input and not the output that's the problem (i.e. waiting for all of the input to be received), but I'm not sure how to go about proving that right now. I'm a bit jammed up with work, but I was hoping to look it over a bit later during the weekend.
----------------------------------------------------------------------------------
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?
January 22, 2010 at 4:59 pm
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:
Heh... take a breath, Adam. 😉
No... I'm no more of a hero than you or anyone else. But there are a huge number (about the same number that use Cursors and While loops inappropriately) of people that use CLR's as the same proverbial hammer that everyone is talking about T-SQL being because they don't know how to do whatever in T-SQL. Apparently, they don't know how to do their job either because if they did, they would have done it in T-SQL. The problem is that a good number of those folks then turn around and write blogs about how good they did (please, don't read into that my blogging friend... you're not a part of the problem) and the disease spreads just like it did with Cursors and While loops.
Not everyone falls into that category and not all CLRs are bad. In fact, this thread has loads of good uses in it by some very good folks. They're not a part of the problem either.
Another point I'm trying to make (perhaps by flinging a pork chop or two) is that even some of the best written blogs and articles about when to use a CLR almost always say the same thing... "when you can't do it in T-SQL" or "when procedural code is required", etc, etc. According to what you and I have BOTH seen on this and other forums, there are a huge number of people who don't even know how to do a joined update and end up using a cursor (hence the "don't know the job" comment"). Folks need to be a whole lot more detailed about when to use a CLR to keep folks like that who really don't know how to do their job in T-SQL out of even more trouble than they're already in.
So far as what these damned things are called, "CLR" works for me because everyone (even you :hehe:) knows what I mean and it's nice and short to type. 😉
As a side bar, your ad hominem attack speaks volumes, as well... I guess it means we'll have to go "dutch treat" on the beer when we finally meet. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2010 at 10:03 pm
Adam Machanic (1/22/2010)
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.
Ah... sorry, Adam. Now I understand the attack. You thought I was attacking you and those like you. My wording may have been both terse and poor, but that couldn’t be further from the truth.
To be clear, I suffer no FUD about CLR's and you are [font="Arial Black"]in[/font]correct about me wanting to stomp out such a useful technology. As you’ll soon see, the FUD I suffer from is with some (most, actually) of the people that write CLR’s and not the use of CLR's.
Lynn is correct about me and my rant being general in nature, bless his good soul. I've seen a lot of damage by a lot of people who really don't know the T-SQL side of their job and have inappropriately used CLR's and other technologies to try to make up for it (especially the ones with a whole bunch of letters after their name). In the last 3 years of employment, I've worked with about 50 different Developers (SQL, GUI, and hybrid), DBA’s (both certified and “volunteered”), and Software Engineers... I can count on one hand those who haven't written a performance damaging Cursor, While Loop, Recursive CTE, Recursive Function, or CLR. I have a manager friend who told me of one developer who didn't know how to do a "merge" and, much like the folks I sighted in my previous post, decided that he could do it with a cursor. When my friend told him "No" to the cursor, that developer (lordy I hate to call him that) built a CLR to do it. :sick:
I've seen the same trend for CLR's (and I'll continue to call them that) that I've seen previously for recursive CTE's and whole bunch of other technologies.... rampant inappropriate use just because they're "new" or they're "easy" for people who don't know how to do their job in T-SQL. I'm as frustrated about those things as you are about the FUD surrounding CLR's. Since my frustration in that area is so high, it frequently comes out as a rant and it was inevitable that you or someone like you would take personal offense somewhere along the line. Guess I have to work on the ol’ people skills. :angry:
Just to set the record straight for all those that believe me to be an anti-CLR monster, I’m not. Ironically, when I first heard of them, my eyes lit up. “What a wonderful way to make up for any short-comings in SQL Server” was my initial thought. Then, the abuse began. Even though we were still stuck in SQL Server 2000 at work, thanks to the CLR “craze”, people rediscovered the “nearly equivalent” (their words, not mine) technology of writing extended stored procedures and started writing them for the most incredibly stupid things because they actually didn’t know how to do their job in T-SQL. Shortly after that “wonderful” revelation on their part (about 4 years ago IIRC), I took up the mantra of “People who use CLR’s don’t really know how to use T-SQL” which, of course, is partially incorrect because there are some very good uses for CLR’s.
Thanks to the wonderful efforts of folks like the incredible Jonathan Kehayias (he and I have gone round-n-round both publicly and privately and not only does he “get it”, but has helped me “get it” and in a very professional manner, as well), R.Barry Young and a host of others on this fine forum, and even you indirectly, I dared to post a Friday poll asking people “What’s in YOUR CLR”? Lot’s of good folks came forward and restored my faith that not everyone is out in left field insofar as CLR’s are concerned.
But there’s still a lot of work to do. Heh… I still hear horror stories of people like that fellow that wrote a CLR to do a simple merge. There are simply too many people (my rant came out as “most users”) that are using T-SQL that know almost nothing about it and are ill equipped to make the decision as to whether “anything procedural” is a good candidate for a CLR because, to them, almost everything needs to be procedural. Those are the people who don’t know how to do their job in T-SQL and probably will never take the time to do so. That’s also a huge additional frustration for me because I've had to (and still do) work with so many people that have that attitude.
So, understand this my fine, fellow frustrated, and highly respected (yep, even by me) friend… we’re actually on the same side. You’re advocating the good uses of CLR’s and I’m doing the dirty work of condemning the bad uses and the moroff’s that don’t care. Try not to be too hard on the crusty ol’ bugger that takes out the trash. :hehe:
I may buy you a beer, yet, to thank you for a favor you probably aren’t even aware of. 😉 I'll tell you when we meet someday.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2010 at 10:09 pm
p.s. There's actually a personal reason why I refer to them as CLR's... I'll tell you that when I meet you, as well. Might require a second beer, though. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2010 at 10:30 pm
Jonathan Kehayias (1/22/2010)
David Portas (1/22/2010)
abhishes (1/20/2010)
I am doing a presentation for all the features of SQL Server, and one of topics is SQL CLR.Intensive byte level operations within large objects are hard or impossible to do efficiently in T-SQL. McLaren use CLR and Filestream features for processing engineering data in SQL Server:
http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000001476
That was a really interesting read, just being a gearhead, not to mention the actual technology behind how they do all that. Thanks for sharing.
Wow! Nice article, indeed. Incredible read. I agree... thank you for sharing it.
What's really amazing is what they called some of the SQL Server 2008 technology they used... [font="Arial Black"]CLR[/font]. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2010 at 9:25 am
Jeff Moden (1/22/2010)
What's really amazing is what they called some of the SQL Server 2008 technology they used... [font="Arial Black"]CLR[/font]. 😛
The article refers to "the common language runtime (CLR)", in addition to "a CLR function". Referring to "the CLR" means that you're talking about the runtime. "CLRs" could refer to multiple runtimes, e.g. hosted in different processes, but it would not make sense to talk about some number of functions, stored procedures, or other modules that way. You would have to qualify those as "CLR modules", just as they did in the article. And no apostrophe is used in either case, because we're not talking about ownership. Hope that clarifies things. 😀
--
Adam Machanic
whoisactive
January 23, 2010 at 10:21 am
Yep... more than you can imagine.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2010 at 1:12 am
The only SQLCLR feature I haven't found a good use for so far is the SQLCLR trigger. Would anyone care if those were removed?
What I would like to see is a SQLCLR analytic operator - something that streams rows in and out - like the Segment and Sequence Project operators used by the likes of RANK and ROW_NUMBER. A sort of combination UDA and STVF...
Being able to extend the analytic and window functions would be awesome, and allow me to shut my Oracle colleagues up for five minutes. It'd be great to be able to stream rows into, and out of, a SQLCLR object. If Microsoft won't include proper analytic functions (LEAD, LAG, SUM OVER PRECEDING and so on) I'll quite happily write my own!
UDAs are already the fastest way to stream rows from SQL Server into a SQLCLR object - blowing away the context connection method. Equally, STVFs are the fastest way to stream data out of a SQLCLR object. Putting them together would open a world of possibilities - combining the power of good compiled code with efficient set-based data access.
IEnumerable-wrapped SqlDataAdapters are an alternative, but having to use an external connection is a serious downside (as is the fact that 2008 broke this method AFAIK). In an attempt to get as close to the data as possible, I have gone so far as to consume a set of rows using a UDA, produce a super-compact representation of the data in a streaming SqlBytes output, and APPLY that to an STVF to process the data and split the binary data back to rows. There are three main problems with this:
1. Passing LOB streaming input to a SQLCLR TVF always throws a wrong thread exception (!)
2. UDA output is only produced from the Terminate() method, which is only called when all rows have been passed to the UDA via the Accumulate method (and possibly Merge()). This means that LOB output from a UDA has to be fully built before it can start to stream. Kinda seems to defeat the purpose of providing a streaming interface, really.
3. SQL Server doesn't call Dispose on any stream wrapped by a SqlChars or SqlBytes object. If a other operators (like hash join) can spill to disk, why can't I?! 😛
4. SQL Server doesn't guarantee the order of rows presented to a UDA. Ok, so I can sort-of work around this with ROW_NUMBER and an extra parameter - but a guaranteed order would make everything so much easier and guarantee memory efficiency.
Anyway...on to other stuff:
As far as I can tell, a T-SQL scalar function that does not access data will always be slower than a SQLCLR equivalent. And, since T-SQL scalar functions should never access data, that's not much of a restriction. T-SQL is interpreted whereas SQLCLR is compiled - so T-SQL seems entirely the wrong tool for scalar functions.
SQLCLR UDTs are very powerful and much underused, though their serialization behaviour could use some work. I was hoping for some optimization in this area for 2008 (which allows UDTs to exceed 8000 bytes) but no. It also amuses me slightly that the Microsoft-provided 2008 SQLCLT UDTs use the UNSAFE permission set...
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply