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
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:
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
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
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 12 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply