The CLR

Question

The CLR

Steve Jones - SSC Editor

SSC Guru

Points: 736063
More actions
May 12, 2008 at 4:56 pm

#187664

Comments posted to this topic are about the item The CLR

Viewing 15 posts - 1 through 15 (of 57 total)

You must be logged in to reply to this topic. Login to reply

Andrew..Peterson SSCertifiable Points: 6838 More actions · Answer 1

Here are my two cents -

I've been working with SQL Server since version 4.21 (1995), and quite frankly, I have not really seen any issues that would be best served using CLR.

And while I have not done any testing - I would guess that access to the actual data via CLR would be slower. But only a guess. And as applications move more of the logic to the business tier, using CLR makes even less sense.

Now, if T-SQL did not support try/catch, I might think CLR would be useful, but not now.

Just because you have a new tool, does not mean it is the right tool.

The more you are prepared, the less you need it.

Jeff Moden SSC Guru Points: 1004281 More actions · Answer 2

Through a bunch of testing across many many threads on this forum, I've found that, with the exception of RegEx replace, someone knowledgeable in the fine art of T-SQL can beat most CLR applications for performance and scalability.

--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)

Cenk OZPAY SSC Enthusiast Points: 161 More actions · Answer 3

CLR?

I have seen some performance advantages when used in scalar functions. CLR can be used when there is no corresponding T-SQL functionality like splitting a string by a delimiter.

Most people agree that it should not be used for accessing data in tables...

Jeff Moden SSC Guru Points: 1004281 More actions · Answer 4

Cenk OZPAY (5/13/2008)
CLR?
I have seen some performance advantages when used in scalar functions. CLR can be used when there is no corresponding T-SQL functionality like splitting a string by a delimiter.
Most people agree that it should not be used for accessing data in tables...

Splitting a string by delimiter is no big deal in T-SQL...

http://www.sqlservercentral.com/articles/TSQL/62867/

--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)

jfmccabe SSCrazy Points: 2547 More actions · Answer 5

Depends what you mean by complex problems. I'm involved in educational admin and we have a standard allocation problem at the start of each academic year.

Each student makes a choice of up to 4 courses, each course has associated classes which in turn have allocated timetable slots. Each timetable slot has a numeric value which is used to construct a bitmap so that we can test for clashes. Each class has a size "limit" which is used as a weight to balance the allocation of students to classes.

As the allocation proceeds, we need to store the numbers in each class and each student's allocated classes. Currently, we draw most of the data down from the server and process it locally. This would seem to me to be the sort of thing that the CLR would be good at since all the work would be done on the server.

Eric Mamet SSChampion Points: 11728 More actions · Answer 6

Could the answer be simple?

Could it be that it's not that useful anyway!

A bit like cursors :)... Sorry, could not help it!

TSql is very efficient at accessing data, CLR is not better, usually much worse.

Application developers go to great length to split problems in little chunks with as clear boundaries as possible for clarity, scalability, etc

Trying to bundle everything within the database server does not seem to help, from that perspective.

Eric

Craig A. Silvis Ten Centuries Points: 1038 More actions · Answer 7

Perhaps another reason that CLR is not being adopted is that it is a lot more involved than using TSQL and for the added complexity there needs to be a compelling reason to go through the pain.

And that reason is just not there.

I spent a week with a co-worker pounding through a CLR application while another co-worker tried it without and he won. Easily.

Of course there was the usual learning curve but even adjusting for that the end result did not justify the extra time spent and the added complexity.

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 8

I'm not adverse to using it, but I've only seen string parsing or complex math solutions that were clearly more viable with the CLR than with TSQL code. Get me good examples that build a case for it and I'll implement it in a heart beat. As long as my main problems are around indexing and SELECT statements... I just don't have any use for it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Someguy SSCrazy Points: 2207 More actions · Answer 9

Like many things, CLR is a tool that helps in certain instances and may be inappropriate for others.

In terms of the pros:

You can add a lot of functionality when you add the power of an upper generation language to your stored procs. Why continue to add new keywords to T-SQL when those functions already exist elsewhere? This also increases the power of your development team. Most teams involve some db people using SQL and other people using languages like C# for other areas of the application. By using the CLR, you can mix and match - this adds great flexibility and speed in development. You've got the people, why not use them? Yes, you can do almost anything with T-SQL if you're an expert, but that doesn't mean T-SQL is the best or quickest way to do it. Then of course there are the "almost always" situations where T-SQL just doesn't have what you need.

In terms of cons:

Yes, you do take a performance hit. That wouldn't be so bad for individual functions, but with SQL Server (at least earlier versions) CLR is either turned ON or OFF on the database level with ON causing some delay even before it's implemented.

We wind up having certain servers where it's ON and some where we avoid using it, depending on performance needs. Having said all of that, we don't use it a lot.

___________________________________________________
“Politicians are like diapers. They both need changing regularly and for the same reason.”

Raj Shekar SSC Eights! Points: 871 More actions · Answer 10

I have used it a couple of times for task that could be done using T-SQL. Its very slow.

Its more useful to write code which could be understood by people who are more acquainted with .NET programming and not databases.

Still need to work and see if it can used better !!!

Raj 🙂

BJ Hermsen Hall of Fame Points: 3123 More actions · Answer 11

ignorant rant of a shot in the dark...

lets say that i am a vendor supplying a backend to a large customer base. I provide an API schematic to them and they all have their own front ends built for both processing and retrieval. CLR could be a great tool to utilize for anything that requires more indepth application coding\logic. I agree that you can do nearly anything and everything in stored proc's but should you do that either? I would be interested to see if an app was designed this way the performance impacts of leaving data retrieval and insert\updates to tsql and manipulation and processing to CLR. This stays serverside in lieu of sending a ton of data down the pipe and back again.

Another interesting effect of this would be in institutions where data privacy is important. If you only send the final absolute result as you did all processing serverside then you shoudl alleviate a lot of data in your pipe as well as processing time being done on teh larger machine.

Maybe my ignorance is strong today but i can see that it could be quite useful.

Eric Mamet SSChampion Points: 11728 More actions · Answer 12

Maybe we can summarise by saying that integrated CLR is the perfect solution...

but that we are still looking for the problem 😛

Jeff Moden SSC Guru Points: 1004281 More actions · Answer 13

emamet (5/13/2008)
Maybe we can summarise by saying that integrated CLR is the perfect solution...
but that we are still looking for the problem 😛

Just couldn't have said it any better than that... 😀

--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)

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 14

I guess most people don't use it, but the replies seem to underscore my point. Most people posting haven't given a specific area where it has or hasn't worked. It would be good to know exactly what's been tried and has or hasn't worked.

The class registration example is interesting, though I'd like to see the implementation. As far as what you've tried that others did better/more quickly, any one want to give examples? Anyone want to write an article that shows it doesn't work well? Maybe someone will find flaws in your code and make it better. Maybe someone will prove you're right.

Splitting a string is easy. Perhaps if you wanted to Proper Case names, split them into 3 (given, middle, sur) separate fields and handle cases like Jr, Sr, von Helsing, etc, the CLR would have an advantage, but that's what I think the problem is.

We don't have examples that allow us to see where it makes sense.