What's in your CLR?
-
For anyone else confused, the SQL CLR is a programming platform inside SQL Server that allows you to write your own assemblies in any .NET language to call from t-sQL and work with data.
http://msdn.microsoft.com/en-us/library/ms189566%28SQL.90%29.aspx
-
Done a fair bit of CLR.
1) TiffPageCount: We have a java-based tiff viewer to display scanned images in our main web site. For multi-page tiff files it requires a count of the pages as well as the location of the document. Our web team was struggling to get a count and I just made a little 10 line CLR that calls system.imaging to get it. Super easy to code but not the best use of a database server.
2) HUGE XML export: We needed to generate a very large XML file for a client - over 200MB. None of the SQL Server tools could do it. VB.net via CLR to the rescue. Personally, I doubt the client ever manages to import the file - but I delivered it.
3) US Standard Claim Forms EDI (X12 - 837): This is basically a full-featured app called from SQLCLR udf's. It manages queueing up records to be added to a file, generates the unencrypted file, encrypts the file using a public key stored in a table (gpg), and can send the encrypted file via FTP, email, or simple network storage. It reports on the entire process and is smart enough to tell users when a record doesn't meet the requirements to send. You can buy X12 exporters but they are VERY expensive and still require a lot of work to set up. All told - for maybe $25k worth of my time I built my employer an app that would have cost them at least $250k to buy. Nope, I didn't get a bonus for it. haha
4) AP XML Feed Import: The Associated Press has an XML format that is too complex for Microsoft products to read. I wrote a VB.net CLR to parse it.
CLR has it's place and I know I've stretched it a little farther than I should. Sometimes what's expedient just takes precedence over what "should" be done.
-
Jack Corbett (12/11/2009)
The reason I don't use the CLR is simple. I haven't had a task where I found it to be necessary. I don't work on big, high transaction systems where milking the a few milliseconds or even seconds of performance is necessary so the time needed to optimize to that level is better used elsewhere. Before I get flamed for saying that, I do attempt to write the best-performing code I can, but I don't have to seek out faster solutions if the customer (end-user) is satisfied.Anecdotally a couple of times I tested CLR vs pure T-sql solutions the pure T-sql solution was faster. I make some very limited use of it, but only in cases where generating the answer in T-sql is very awkward but for one reason or another I want the functionality at the SQL Server level instead of in the application.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/ -
Sorry for my apparent absence, but I didn't want to put any kind of a slant on the possible responses.
I have to admit a great amount of surprise at the responses. I've seen very little blanket "anything that can't be done in SQL", which usually equates to "don't know how" and I had lot's of pork chops ready for those types of folks :-P. Most of the places where folks have stated they use CLR's seem to be very well thought out, practical, and truly should be done somewhere besides the bowels of T-SQL (heh... although I may have to write an article on how to do very high speed medians in a set based fashion) including Regex, certain string manipulations, hierarchies, communications to Web Servers and other "devices", certain types of mailings, and file handling (my favorite).
Personally, I'm like some of the others... there hasn't been much that I haven't been able to do in T-SQL, I frequently get close enough to or even beat CLR speeds to make it not worthwhile maintaining another code base, and the GUI folks normally take care of all the Web Server and "other" device requirements from the app.
Anyway, thank you one and all for the time you spent on some really good answers. I also really appreciate those of you that have CLR's running and still warn that they can easily be overused or inappropriately used. I also appreciate those who don't have any active CLR's that say they can see some appropriate uses.
What's probably more impressive is that this could have turned out to be an absolute flame war between CLR users and the hard core T-SQL folks. Instead, this thread is filled with some very thoughtful and useful ideas as to what folks are actually using CLR's for or not and why.
Very well done, folks, and thank you all again.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ive only used it to transpose a dataset, which I later found out could be done with unpivot.
-
Tao Klerks (12/11/2009)
Registry Access. Haven't figured out an efficient and sensible way to do it in SQL Server 2005 without using the CLR.Powershell for a decent bit of it. Not necessarily actual registry hacking, but there's a lot you can change with PS.
-
Dave Ballantyne (12/11/2009) CLR routines are again (over)used by non-set based mindset developers.
But if the requirements are not "set-based" or "column-based" ....
For example, if the ROW (condition) then select col_A as @vrbl_X, col_B as @vrbl_Y
else, select L, M, N, O, P, Q, ...V and arrange them into several lines of a flat file according to X12 rules, inserting @vrbl_X, @vrbl_Y, and other globals at the appropriate places, and updating the globals conditionally as you go along.
Sure, a stored procedure CAN do it, but a script component or other CLR is easier. Especially when the DB is read-only and you can't stick a sproc into it!
-
Wesley.Groleau (7/27/2013)
For example, if the ROW (condition) then select col_A as @vrbl_X, col_B as @vrbl_Y
else, select L, M, N, O, P, Q, ...V and arrange them into several lines of a flat file according to X12 rules, inserting @vrbl_X, @vrbl_Y, and other globals at the appropriate places, and updating the globals conditionally as you go along.
Sure, a stored procedure CAN do it, but a script component or other CLR is easier. Especially when the DB is read-only and you can't stick a sproc into it!
I guess my question would be, why do you think that couldn't be done in a set-based or column-based fashion? Remember that "Set-Based" doesn't mean "all in one query". And just because a database is "Read-Only" doesn't mean you can't have a stored proc that runs against it.
So far as "a script component or other CLR is easier" goes, that depends on a whole lot but mostly depends on the knowledge of the programmer both about T-SQL and the "other" language and the actual problem itself, whatever it is.. Are they smarter on the declarative or procedural side of the house? I agree that I've seen some folks that have worked absolute wonders using CLR (especially SQLCLR) as it was meant to be used. I've also seen some people do some absolutely stupid things with it just because they weren't as familiar with T-SQL as they were with typical front-end-style programming.
Of course, that's not just limited to T-SQL. 😉
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (7/27/2013)
Wesley.Groleau (7/27/2013)
(snip) Sure, a stored procedure CAN do it, but a script component or other CLR is easier. (snip)I guess my question would be, why do you think that couldn't be done in a set-based or column-based fashion?
If I thought that, would I have said what I didn't snip?
If you instead ask "why do you think that shouldn't," my answer would be "because I've done both"
If you haved a screen full of declares and ifs and loops, and are storing values as you go along to be used in later iterations, you are doing procedural programming and might as well use a language originally intended for that.
If you are able to avoid either by using nested subqueries (yes, I've done that too, four levels deep), then there's a high probability the next person who has to work on it won't understand it, and will just start over. Or break it.
I know others disagree, but for me, the priorities are
1. Meets the requirements
2. Easy to understand
3. Robust
4. Amenable to re-use
5. Easy to write
6. Efficient
(1) is obviously the first, but (2) makes 1/3/4/5 more likely. And an irrational commitment to (6) often screws up all the others, including (1).
-
Question Requirements!
-
KermitTheRock (7/28/2013)
Question Requirements!Yes, to make sure they are correct. Once that is established, they are first priority.
Well, not always. In some circumstances, requirements can be changed for the convenience of the company.
But not in healthcare, communications, transportation, defense, finance, generating/managing electricity, ....
So, pardon my tunnel vision--I've been full-time in at least one of those since 1974. (But only doing software since 1985).
Except for twenty months finding out that teaching, much as I enjoyed it, doesn't pay the bills. And now that I no longer have the bills, I'm too old to switch. But that's off-topic.
-
LerxtDBA (12/14/2009)
You can buy X12 exporters but they are VERY expensive and still require a lot of work to set up.That (and import as well) is where I would rather not do SQL, though I have actually done both (which is how I learned they're not the best use of my time).
There are free X12 tools, but every one I have ever looked at (free or not) handles only the syntax level--the easy part. They expect the user to define the semantics and business logic in XML (or worse) that is more complicated than the entire program would be in VB or C#
-
Wesley.Groleau (7/28/2013)
Jeff Moden (7/27/2013)
Wesley.Groleau (7/27/2013)
(snip) Sure, a stored procedure CAN do it, but a script component or other CLR is easier. (snip)I guess my question would be, why do you think that couldn't be done in a set-based or column-based fashion?
If I thought that, would I have said what I didn't snip?
If you instead ask "why do you think that shouldn't," my answer would be "because I've done both"
If you haved a screen full of declares and ifs and loops, and are storing values as you go along to be used in later iterations, you are doing procedural programming and might as well use a language originally intended for that.
If you are able to avoid either by using nested subqueries (yes, I've done that too, four levels deep), then there's a high probability the next person who has to work on it won't understand it, and will just start over. Or break it.
I know others disagree, but for me, the priorities are
1. Meets the requirements
2. Easy to understand
3. Robust
4. Amenable to re-use
5. Easy to write
6. Efficient
(1) is obviously the first, but (2) makes 1/3/4/5 more likely. And an irrational commitment to (6) often screws up all the others, including (1).
Me too. I mean that I've worked on many complex problems (admittedly, not specifically EDI X12, though) where people have done things like ...
If you haved a screen full of declares and ifs and loops, and are storing values as you go along to be used in later iterations, you are doing procedural programming and might as well use a language originally intended for that.
What I have found is that many people resort to such RBAR in SQL Server because they don't know of a set-based manner to tackle the problem. The presence of such things doesn't necessarily mean that a set-based solution isn't possible.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
i have a big proof of concept CLR project that is filled with items i either create myself, or find here on SSC.
I just keep adding to it as I encounter ideas.
some of the things I've done in it:
Regular Expressions.
Read Write Files and Folders, including binary/blobs.
Send Email.
Read Email via POP3.
Read Email via IMAP.
PGP Encryption/Decryption.
AES Encryption/Decryption
Zip/Unzip Files.
Send/Get files via FTP and SFTP.
Convert RTF to text/text to RTF/HTML to RTF.
Export a table or query to multiple formats via a web service.(excel/word/pdf/mht/html/more)
A different write to PDF function.
Read Excel without Office DLL's.
HTML Encode/Decode functions, UrlEncode/Decode Functions.
The usual caveat here applies, just because it can be done in TSQL, or CLR, does not mean it's
the right thing to dothe right place to do it.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (7/30/2013)
i have a big proof of concept CLR project that is filled with items i either create myself, or find here on SSC.I just keep adding to it as I encounter ideas.
some of the things I've done in it:
Regular Expressions.
Read Write Files and Folders, including binary/blobs.
Send Email.
Read Email via POP3.
Read Email via IMAP.
PGP Encryption/Decryption.
AES Encryption/Decryption
Zip/Unzip Files.
Send/Get files via FTP and SFTP.
Convert RTF to text/text to RTF/HTML to RTF.
Export a table or query to multiple formats via a web service.(excel/word/pdf/mht/html/more)
A different write to PDF function.
Read Excel without Office DLL's.
HTML Encode/Decode functions, UrlEncode/Decode Functions.
Those are some pretty good ideas for CLR. On the one for reading/writing files, did you check to make sure that any parameters passed to them were "DOS Injection" proof?
The usual caveat here applies, just because it can be done in TSQL, or CLR, does not mean it's
the right thing to dothe right place to do it.Fully agreed but you also have to remember something else. Different companies and organizations (the Department of Defense, for example) have different rules/restrictions/availabiliuty on what can be used and you can't always use the "right place" to do it but the job must still be done.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply