April 19, 2009 at 10:45 am
Paul White (4/19/2009)
A parting example. An application currently does its business-logic validation in middle-tier classes, written in a variety of .NET languages, and there is a desire to move the validation code to the database, would you:a) Cancel all leave and rewrite the logic in T-SQL; or
b) Reference the existing classes in a SQL CLR routine, finish by lunchtime and take the rest of the week off? 😀
"It Depends" on the underlying reason as to why they want to move the logic. If it's to make it so that one doesn't need to be well versed in "C", Java, VB, Perl, Pascal, and all the other languages the validations were written in, then (a) could be the proper choice. This would be especially true if a big project was looming that would affect all of that business logic and they wanted to share the load between the GUI programmers and the database folks. Shifting gears, it may be that the suggestion of moving it to the database is the wrong suggestion. It may be that the correct suggestion is to do it all in a single compiled language like "C#" or VB.Net. Finishing by lunchtime would certainly be nice, but it may not meet the real business reason for the move.
I agree that CLR's are a tool that allows homemade extensions to the language to be built fairly easily. It's the next best thing to "open source". But, some folks have attempted to turn the use of CLR's into a panacea for all problems that they can't seem to do (for shear lack of knowledge of T-SQL) in T-SQL with close or equivalent performance.
For example, I'm going through a huge amount of legacy code at work trying to figure out what it all does because there's no documentation. Now, it wasn't a CLR but could just as easily have been... I found a UDF that did the simple Modulo calculation and another that used a While Loop to produce the number of days between two dates. Heh... and it wasn't like they were trying to skip weekends or holidays. It produced exactly the same result that DATEDIFF would produce. Like I said, it could just as easily been a CLR and probably would have been if it were in 2k5 instead of 2k.
Ok... now before you get your gun :hehe:... I agree... especially lately with some of the good tests between some T-SQL solutions and some CLR solutions that I've (finally) seen on this very forum. There are very few absolutes in the world of databases. If you have folks on-site that can write some decent CLR solutions, you'd actually be more "nuts" for not seeing what they can come up with and doing a little testing between the CLR's and the T-SQL solutions that good database people can come up with.
Personally, I rather see CLR's that added generic utility be written such as some of the financial functions available on some $10 calculators, the ability to interogate a "directory" without having to use sp_OA* or xp_CmdShell, parameter splits (like we've already seen), or simple dynamic crosstabs (to name a few).
I may have to fire up VB.net or learn "C#" just to make some of those things come true.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 19, 2009 at 11:48 am
Jeff Moden (4/19/2009)
I may have to fire up VB.net or learn "C#" just to make some of those things come true.
Well, if you won't run anyone else's compiled code, what other choice is there?
[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]
April 19, 2009 at 3:21 pm
Jeff Moden (4/19/2009)
"It Depends" on the underlying reason as to why they want to move the logic.
That's true. My point was that if the validation code already existed in .NET classes, it would be very easy to move.
Jeff Moden (4/19/2009)
But, some folks have attempted to turn the use of CLR's into a panacea for all problems that they can't seem to do (for shear lack of knowledge of T-SQL) in T-SQL with close or equivalent performance.
Also true. It's not much fun replying to stuff I just agree with. Could you say something controversial next time please? 😉
Jeff Moden (4/19/2009)
I found a UDF that did the simple Modulo calculation and another that used a While Loop to produce the number of days between two dates.
And this why we will never be short of work 🙂
As far as CLR usefulness is concerned, my position is that the overhead (switching languages and IDEs for a start!) means that I always but always try to find a good T-SQL solution first. If T-SQL can't do 'it' or do 'it' well, I might fire up BIDS. Note though that there is some extremely funky stuff you can do in C# (a very different animal from 'C'). Of course anything you can do with CLR can be done in 'real' code in the middle-tier or front-end, but sometimes it makes sense to keep the functionality within the SQL Server.
One more example from recent times: there was a need to check the network status of 2,500 remote MSDE instances, and retrieve the lastest record_id on some table from each. A multi-threaded (try that in T-SQL) CLR routine that sent a network ping and, if successful, queried the record_id value was the solution. It was wrapped in a T-SQL procedure and run as a job. Now sure, you could write an external application, service or whatever to do the job - but this was quicker, simpler, and arguably more resilient.
No guns were used in the writing of this response. I enjoy our little 'chats' 🙂
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 19, 2009 at 5:57 pm
Paul White (4/19/2009)One more example from recent times: there was a need to check the network status of 2,500 remote MSDE instances, and retrieve the lastest record_id on some table from each. A multi-threaded (try that in T-SQL) CLR routine that sent a network ping and, if successful, queried the record_id value was the solution. It was wrapped in a T-SQL procedure and run as a job. Now sure, you could write an external application, service or whatever to do the job - but this was quicker, simpler, and arguably more resilient.
You can do this in T-SQL. Including the multi-threaded part.
I'm not saying that it would be better, but ..., you CAN do it.
[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]
April 19, 2009 at 6:37 pm
Paul White (4/19/2009)
As far as CLR usefulness is concerned, my position is that the overhead (switching languages and IDEs for a start!) means that I always but always try to find a good T-SQL solution first. If T-SQL can't do 'it' or do 'it' well, I might fire up BIDS. Note though that there is some extremely funky stuff you can do in C# (a very different animal from 'C'). Of course anything you can do with CLR can be done in 'real' code in the middle-tier or front-end, but sometimes it makes sense to keep the functionality within the SQL Server.One more example from recent times: there was a need to check the network status of 2,500 remote MSDE instances, and retrieve the lastest record_id on some table from each. A multi-threaded (try that in T-SQL) CLR routine that sent a network ping and, if successful, queried the record_id value was the solution. It was wrapped in a T-SQL procedure and run as a job. Now sure, you could write an external application, service or whatever to do the job - but this was quicker, simpler, and arguably more resilient.
Heh... dangit... you're no fun either! 😀 Can't find a bloody thing to argue with on that...
No guns were used in the writing of this response. I enjoy our little 'chats' 🙂
... and then to actually brag about it... oh, the nerve! :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
April 19, 2009 at 6:53 pm
RBarryYoung (4/19/2009)
You can do this in T-SQL. Including the multi-threaded part.I'm not saying that it would be better, but ..., you CAN do it.
Well Jeff's no fun anymore 😉 but hey, welcome Barry.
Go on then. Tell me how to do it in T-SQL (including the ping - and if you use xp_cmdshell, well...!).
By the way, only multi-threaded examples will win the prize - multi-process is not the same at all!
😀
Paul
edit: BTW my motto: just 'cos you CAN do something, does not mean you SHOULD :w00t:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 19, 2009 at 8:28 pm
Paul White (4/19/2009)
By the way, only multi-threaded examples will win the prize - multi-process is not the same at all!
Hah. Unfortunately, I am old enough to know that there is no real difference! Especially with respect to the functional specs of this problem:
Steps:
1. Write sProc: spCheckOneMsdeInstance
2. Create a Service Broker Service & Queue
3. Write an activation sProc for the queue that calls spCheckOneMsdeInstance passing the Instance name received from the queue message.
4. Set activation count on the queue to 50 and disable activation
5. Queue up 2500 messages, one for each instance to check
6. Enable activation on the queue and stand back
[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]
April 19, 2009 at 8:53 pm
Barry,
That's very clever - I hadn't thought of that. Points to you!
Without being churlish though, I would argue that multi-process is very different from multi-thread:
* Processes are heavy-weight and more expensive to start and stop than threads
* Cross-thread communication is much cheaper than cross-process
* Windows has excellent threading support for this kind of task - running multiple processes is a bit of a kluge.
That's just in passing though, and only because you mentioned it.
Your service broker solution is 😎
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 19, 2009 at 9:42 pm
Paul White (4/19/2009)
Barry,That's very clever - I hadn't thought of that. Points to you!
Without being churlish though, I would argue that multi-process is very different from multi-thread:
* Processes are heavy-weight and more expensive to start and stop than threads
* Cross-thread communication is much cheaper than cross-process
* Windows has excellent threading support for this kind of task - running multiple processes is a bit of a kluge.
That's just in passing though, and only because you mentioned it.
Your service broker solution is 😎
Paul
Heh, thanks.
[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]
April 19, 2009 at 9:58 pm
RBarryYoung (4/19/2009)
Heh, thanks.
It's just occurred to me that there was a great opportunity back there for you to use a line like:
"I find your lack of faith (in T-SQL) disturbing..."
(c) D.Vader
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 19, 2009 at 10:11 pm
Paul White (4/19/2009)
RBarryYoung (4/19/2009)
Heh, thanks.It's just occurred to me that there was a great opportunity back there for you to use a line like:
"I find your lack of faith (in T-SQL) disturbing..."
(c) D.Vader
Yeah, but I promised Jeff that I wouldn't "Admiral Ozzel" anyone for a while. 🙂
[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]
April 20, 2009 at 5:30 am
RBarryYoung (4/19/2009)
Paul White (4/19/2009)
RBarryYoung (4/19/2009)
Heh, thanks.It's just occurred to me that there was a great opportunity back there for you to use a line like:
"I find your lack of faith (in T-SQL) disturbing..."
(c) D.Vader
Yeah, but I promised Jeff that I wouldn't "Admiral Ozzel" anyone for a while. 🙂
Heh... it's the only way he can keep a promise to himself, lately. 😛
Nah... that kind of stuff is fun. The stuff you were into was a bad batch.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2009 at 12:54 pm
File handling is one of those cases when CLR comes in very handy. I wrote a small CLR procedure in C# (I dont know anything about C#, some how managed it) that opens the file and appends a line into the file. I plan on invoking the TSQL sp via a report that accepts paramters and passes them to the sp, sp will process and build each record and will call the CLR to write to the file. As far as I know there is no better way to do file handling in TSQL. The file to be written is a variable length text file.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply