Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

What's in your CLR? Expand / Collapse
Author
Message
Posted Thursday, December 10, 2009 8:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:12 PM
Points: 35,808, Visits: 32,482
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #832695
Posted Friday, December 11, 2009 2:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
The only CLR routine that I have written , that wasnt "hello world", is to call and mail a SSRS report.

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/direct-emailing-of-ssrs-reports-via-sqlclr.aspx

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.




Clear Sky SQL
My Blog
Kent user group
Post #832763
Posted Friday, December 11, 2009 3:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 28, 2014 8:19 AM
Points: 7, Visits: 64
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.
Post #832787
Posted Friday, December 11, 2009 4:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, December 8, 2014 3:17 AM
Points: 246, Visits: 384
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.
Post #832814
Posted Friday, December 11, 2009 5:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, December 22, 2014 3:26 AM
Points: 14,205, Visits: 28,536
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 Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #832851
Posted Friday, December 11, 2009 6:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
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.
Post #832856
Posted Friday, December 11, 2009 6:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 10:57 AM
Points: 10,387, Visits: 13,454
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #832858
Posted Friday, December 11, 2009 6:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:46 PM
Points: 388, Visits: 1,047
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
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #832859
Posted Friday, December 11, 2009 6:14 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 12:11 PM
Points: 771, Visits: 1,971
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 --
Post #832860
Posted Friday, December 11, 2009 6:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 12,965, Visits: 32,525
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #832881
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse