June 11, 2008 at 8:03 am
Well, if we're looking for examples, a Fourier Transform would proabably be a good candidate for CLR.
[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]
June 11, 2008 at 8:28 am
nenad.prekupec (6/11/2008)
You ridicule the performance tests that people do that show CLR performance is slow and you offer up that very poorly written performance test that uses a While loop instead of a good set based solution as an argument and then call it from embedded SQL? Heh... you better check again. Check out what a "Numbers" or "Tally" table solution will do for that type of solution... it handily beats the CLR solution. Matt Miller and I have run many tests on this very subject and about the only thing that T-SQL can't beat a CLR on is a reasonably complex RegEx replace and then T-SQL still comes close. And he's NOT anti-CLR! How about a million row running total? Wanna have a race there? 😉
Well ok.. I might not have given proper link... my point was not arguing with T-SQL gurus, and as said writing proper T-SQL in most of cases will be better...
But in my defense I can say, tests that I have done before (sorry if I can't put results here as I don't have them any more) proved me that CLR was quicker then T-SQL... but as I have said I used it for math functions..
As I have said usage of T-SQL vs. CLR depends on the usage....
Anyway we could do some testing when i'll have more time... just to say when to use CLR and when to use T-SQL... nothing more
Well, to restate where I think we are - there are quite a few scenarios where CLR FUNCTIONS are faster than T-SQL FUNCTIONS. For one - they're compiled, so they tend to run quite a bit faster. The problem is - damn near ALL functions tend to suffer performance issues, which cause other parts of the query being executed to not perform well. The end result is that most operations being done with UDF's end up becoming more efficient when rewritten to NOT use the UDF (be it CLR or T-SQL). (Inline functions are usually the exception, since they tend to get treated like views; they get replaces by their text at execution time, so they have a better chance of generating a decent execution plan).
So - you mostly end up with T-SQL statements that end up running circles around the CLR functions, not so much due to any implicit lack from the CLR side, but due to the perf challenges the function calls themselves face.
That being said (and I'm paraphrasing the SQL CLR Best practices) - pretty much anything CLR involving data access starts with a substantial disadvantage it is not likely to dig itself out of. It also doesn't help that CLR UDF's can't really access all of the CLR streaming functions, so you end up having to materialize the entire IEnumerable object before dequeuing any data (which is NOT an issue with CLR proc's), or that SQLCLR is working withing a VERY constricted space to begin with and is therefore prone to resource issues.
Don't get me wrong - advanced math, advanced string manipulation (or pattern-matching), tapping into OS-level stuff, are all places where CLR should at least be considered, because it has a lot more built-in hooks, bells, whistles, features, call them what you will, that T-SQL might not have. That being said - we often then start treading near that theoretical "does this feature belong IN my SQL instance, or would it be better served by being run outside" discussion. So you tend to end up with precious few useful scenarios where the feature SHOULD be in the database AND CLR ends up being more efficient/better than a good T-SQL solution.
From my perspective - you usually have to start by finding something where "native" SQL Server options just plain won't be very good at, and that's where CLR might have a chance to outperform the native solution. The fun is - someone really good with T-SQL (like Jeff and others on here), have a lot of tricks at their disposal, and the SQL Server dev team has done a good job at optimizing a LOT of things, so it really doesn't leave a lot of room to maneuver.
----------------------------------------------------------------------------------
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?
June 11, 2008 at 8:34 am
Jeff Moden (6/11/2008)
Hey Matt... if you see this and remember what the URL is for all that testing we did, would you mind posting it here? I've lost track of it...
I'm running in 16 directions these days - but I will see if I can dig up that testing.
----------------------------------------------------------------------------------
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?
June 11, 2008 at 8:54 am
Thank you Matt,
very nice description of some things I have said or tended to say but lost them somewhere in between.
Just thinking to say CLR is slower then T-SQl, or otherwise is wrong.... it depends what are you trying to do and what way will you go... CLR or T-SQL.... sometimes is better one and sometimes the other as we have seen in Matt's and some other previous posts.
Cheers!
June 11, 2008 at 9:55 am
rbarryyoung (6/11/2008)
Well, if we're looking for examples, a Fourier Transform would proabably be a good candidate for CLR.
Heh... ok, I gotta ask... has anyone actually ever done a Fourier Transform in a database?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 11, 2008 at 10:23 am
Jeff Moden (6/11/2008)
rbarryyoung (6/11/2008)
Well, if we're looking for examples, a Fourier Transform would proabably be a good candidate for CLR.Heh... ok, I gotta ask... has anyone actually ever done a Fourier Transform in a database?
I'm still trying to remember what the heck you use a Fourier transform for anyway....:)
The link that made the most sense to me was this one[/url], but it didn't go incredibly far into details...:):hehe:
----------------------------------------------------------------------------------
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?
June 11, 2008 at 10:35 am
Matt,
Here is a link to Wikipedia that may help jog your memory on thier use:
http://en.wikipedia.org/wiki/Fast_Fourier_transform
Can I personally think of a database application that would use it, not off hand, but I'm sure there are some out there!
😎
June 11, 2008 at 11:10 am
Jeff Moden (6/11/2008)
rbarryyoung (6/11/2008)
Well, if we're looking for examples, a Fourier Transform would proabably be a good candidate for CLR.Heh... ok, I gotta ask... has anyone actually ever done a Fourier Transform in a database?
That sounds like a challenge...!
Seriously though, "useful" and "appropiate" were not part of your specs. 🙂 They're a lot harder to fulfill. (Though as Lynn says, it is possible to come up with applications for them in a database).
[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]
June 11, 2008 at 11:43 am
Matt Miller (6/11/2008)
Jeff Moden (6/11/2008)
rbarryyoung (6/11/2008)
Well, if we're looking for examples, a Fourier Transform would proabably be a good candidate for CLR.Heh... ok, I gotta ask... has anyone actually ever done a Fourier Transform in a database?
I'm still trying to remember what the heck you use a Fourier transform for anyway....:)
The link that made the most sense to me was this one[/url], but it didn't go incredibly far into details...:):hehe:
Here's my exposure to them...
http://en.wikipedia.org/wiki/Spectrum_analyzer
--Jeff Moden
Change is inevitable... Change for the better is not.
June 11, 2008 at 11:46 am
Jeff Moden (6/11/2008)
Matt Miller (6/11/2008)
Jeff Moden (6/11/2008)
rbarryyoung (6/11/2008)
Well, if we're looking for examples, a Fourier Transform would proabably be a good candidate for CLR.Heh... ok, I gotta ask... has anyone actually ever done a Fourier Transform in a database?
I'm still trying to remember what the heck you use a Fourier transform for anyway....:)
The link that made the most sense to me was this one[/url], but it didn't go incredibly far into details...:):hehe:
Here's my exposure to them...
So we want to rework SSMS into a graphical oscilloscope? I could see that....:Whistling:
----------------------------------------------------------------------------------
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?
June 11, 2008 at 11:48 am
rbarryyoung (6/11/2008)
That sounds like a challenge...!Seriously though, "useful" and "appropiate" were not part of your specs. 🙂 They're a lot harder to fulfill. (Though as Lynn says, it is possible to come up with applications for them in a database).
Well.... maybe to do some "trend" analysis in a wierd sort of way, huh? I mean spectral analysis doesn't actually need to be related to sound or frequency of an electrical signal... maybe it could be used on the ol' "People who bought A and B, also bought C" type of problem...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 11, 2008 at 11:48 am
Matt Miller (6/11/2008)
Jeff Moden (6/11/2008)
rbarryyoung (6/11/2008)
Well, if we're looking for examples, a Fourier Transform would proabably be a good candidate for CLR.Heh... ok, I gotta ask... has anyone actually ever done a Fourier Transform in a database?
I'm still trying to remember what the heck you use a Fourier transform for anyway....:)
The link that made the most sense to me was this one[/url], but it didn't go incredibly far into details...:):hehe:
Well the Fourier Transform is arguably the most useful numerical method ever discovered, however, its use is somewhat obscure to the general public, so its application generally remains known only to certain fields of engineering, science and (suprisingly) market analysis. This link has a good starting list of its applications: http://en.wikipedia.org/wiki/Discrete_Fourier_transform.
Lynn's link is actually about the FFT [Fast-Fourier Transform] which is the amazing algorithim that makes the Foruier Transform (the method) efficient enough to be usable.
To try to keep it to a simple explanation: a Fourier Transform changes a numerical data feed (or signal) from Amplitude-over-Time to Amplitude-over-Frequency and vice-versa (they are compliments of one another, so the same method works both ways). While this may not seem like a big deal at first, it affects and has applications to virtually every aspect of the Information Age that we live in today. An obvious example that I use all the time is Digital Audio: most of the audio effects used in processing sound for human listening go though one or more FT's to selectively measure, control and respond to different frequencies.
It has obscure applications also: it turns out that FT's are intrinsically linked to another numerical method/operator called "Convolution" which can be used for all kinds of numerical analysis, including things like calculating the Determinant of a Matrix (which itself has many, many applications) and as a consequence, many numerical measures of matrix or vector-based data can be done using FT's. Now for a long, long time it was widely believed that there was no efficient way to compute the Convolutons of large data sets, and thus no efficient way to calclate these matrix measures. However, once the FFT was discovered it was soon realized that this could be used to quickly calculate Convolutions and thus all kinds of scientific analysis in everything from Quantum Mechanics to Hydrogeology.
[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]
June 11, 2008 at 11:53 am
Heh... and Marketing.
Nice explanation, Barry!
--Jeff Moden
Change is inevitable... Change for the better is not.
June 11, 2008 at 11:59 am
You're right - that is a good definition. That's kind of what I was getting out of it. Of course - the second thought was - "gee - I sure would love to be in a org that has the expertise and the time to actually Fourier Transform their data". Having the data to even back that up in any meaningful way sounds like a staggering concept.
----------------------------------------------------------------------------------
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?
June 11, 2008 at 12:20 pm
Matt Miller (6/11/2008)
You're right - that is a good definition. That's kind of what I was getting out of it. Of course - the second thought was - "gee - I sure would love to be in a org that has the expertise and the time to actually Fourier Transform their data". Having the data to even back that up in any meaningful way sounds like a staggering concept.
I hear that.
[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]
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply