What's in your CLR?
-
Comments posted to this topic are about the item What's in your CLR?
--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) -
The only CLR routine that I have written , that wasnt "hello world", is to call and mail a SSRS report.
Just like cursors and udfs , from what i see anecdotally, CLR routines are again (over)used by non-set based mindset developers.
They do have a place, interestingly in the latest Phil Factor Speed Phreak Challenge a CLR routine is presently top of the leader board. I suspect though, if and when we get a "ROLLING_BALANCE()" function the need will be even less.
-
We are looking at the Logi reporting tool. We want to report on data in an old 3GL system, so we use a CLR to get the data into a SQLServer view via a web service. So far it is the only way I have found of using a WS in SQL Server.
-
A developer once "proved" to me how much faster CLR was. He developed a simple TSQL function and a CLR function that did equivalent things. His test script showed the CLR function to be roughly 20 times faster than the TSQL function.
On closer examination of what was wrapped around the functions in the test scripts, there were further efficiencies to be gained. For example, the CLR function was only being called "when needed", but the TSQL function was called for every record.
Once the maximum efficiencies were included, the TSQL sample ran slightly faster than the CLR sample.
We do some specialty stuff which requires developing 20,000-30,000 lines of TSQL code in our test environment, moving that to a customer's test environment, then ultimately moving that to the customer's live environment and running it one time (data conversion). Therefore the code needs to be very portable.
Collecting the full set of stored procedures into one text file (we have a script that does this) has proven to be a very effective method for portability. (Server names can also be changes by a simple search/replace across the text file.) CLR adds another level that would also have to be ported.
In short, our developers can use CLR if desired and if effective for what they need to do. Because they also deliver the solution, they also have to work with the portability aspects. To date, only two of over 100 projects has used CLR.
-
I have yet to find a need for writing a CLR package of any kind, so I'm not using CLR directly.
However, I do use CLR quite a lot. We have more and more apps taking advantage of the spatial data type.This is a CLR data type and the functionality it provides is through CLR.
I know that Adam Machanic has found some places where CLR is doing amazing stuff, but you'd need him to detail it where he can.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Registry Access. Haven't figured out an efficient and sensible way to do it in SQL Server 2005 without using the CLR.
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
-
I have not yet used the CLR. As Grant mentioned I would bet more people on 2008 are if you include the Spatial and Hierarchy data types which are CLR types.
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
I've used SqlCLR once in Production.
I ETL'd some data from a DB2 database on iSeries into SQL Server 2005. The data included product descriptions, and for some reason the trademark symbol (™) refused to display as a printable character. I tried every T-SQL trick I could think of to search and replace that character, and for whatever reason, I could not figure out how to find the unprintable character in the column.
I wrote a search and replace function that accepted a database name, schema.table name, column name, search string, and replace string. It worked very well.
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
-
I'm posting as someone who in previous life wrote a lot of C++
Obviously any set based operation would be better in SQL.
Other types of business logic, or a complex custom conversion function would probably be better in CLR (Some SQL string functions are quite slow, and could be better managed directly in memory by a procedural language with pointers).
And there is nothing in sql that is comparable to the heirarchical inheritance structure of C++ (or even C#, which is more limited).
...
-- FORTRAN manual for Xerox Computers --
-
converting an rtf string to raw text.
AFAIK, you can't uses a System.Windows.Forms.RichTextBox in a CLR, but you can call a web service that uses the System.Windows.Forms.RichTextBox;
two steps to do the same process, CLR>>WebService, but it gets the job done.
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! -
Just some string manipulation functions, such as Levenshtein and InitCap. All of which could be coded in TSQL but are easy to do in C# (InitCap is a one-liner).
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I have used SQL CLR code in a specific case after a lot of testing of alternatives coded in VB6/ADO, VB.NET/ADO.NET, and TSQL. The objective in each case was to return a 1000 (or so) row recordset where the contents of each record were determined by a metadata table that specified the table, view, utf, or sproc and the column of the data. The metadata table is dynamic and can change frequently.
As each solution was developed it was optimized for performance within its own technology. All solutions returned the same recordset. The CLR coded solution was roughly 1000 times faster than the worst case (VB6/ADO) and 100 times faster than TSQL. Each solution was tested over 100 times on each of three databases and the results combined in order to determine the results.
I put a lot of work into the development, optimization, and testing of the solutions as it was very important to providing good performance to my customers. The CLR solution is used tens of thousands of times a day by hundreds of users.
Like any tool, SLQ CLR solutions can provide the best solution in the appropriate situation. They can be misused as can any tool.
Ross
-
1. Have not written CLR functions yet. This is because I haven't encountered a situation where T-SQL couldn't cut it so I haven't looked into them thus far.
2. N/A
3. N/A
4. If I had some kind of really intricate string manipulation or needed something in the .NET that I couldn't figure out how to do in T-SQL I'd look into it. So far I haven't come across anything like that.
-
I've written one clr. It was a work around for being able to call an application on a backend AS400 without having access to Microsoft's OLEDB provider for DB2. That provider is only available with the Enterprise Version of SQL Server and since SQL Server is secondary in our environment, Enterprise was not an option. My clr function uses ODBC to connect to the 400 and execute a single program. The biggest pain of the whole thing was getting the proper permissions set for the stored procedure to actually run. Thank goodness for all the blogs/documentation/help on here and Microsoft to try and solve the errors I received on every step of getting it to work!
-
The stuff that I've used CLRs for are things that, as far as I know, can't be done using the native SQL environment.
In particular, at times I require code which sends a HTTP web request and receives a response. This task needs to be executed on a periodic basis - once per hour to be exact.
In order to accomplish this task, I've found only three solutions. One is to have the web request source executing the task on the periodic basis, and updating the database. This is not an option, since the web request is from a third-party, and I can't easily have that third party modify their application to communicate with mine. Another option is to have a Windows scheduled task which executes the web request and then updates the database. The final option was to have a database job which would execute on a periodic basis. This one proved easiest to manage, and all it required was for me to write a .NET CLR which would execute a HTTPRequest and return back the HTTPResponse string.
The other instance that I've used CLRs is when I needed to move files between servers. I couldn't find a way to do this through the native SQL interface, so I wrote a CLR which would handle moving the files between servers. I have found it possible to do it in native SQL using the xp_cmdshell extended stored procedure, but the limitations for that are that it becomes *very* tricky to handle mapping network drives and giving permissions to the users such that the files can be copied between servers.
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply