May 12, 2008 at 7:42 pm
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.
May 12, 2008 at 9:00 pm
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
Change is inevitable... Change for the better is not.
May 13, 2008 at 12:31 am
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...
May 13, 2008 at 12:45 am
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
Change is inevitable... Change for the better is not.
May 13, 2008 at 1:38 am
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.
May 13, 2008 at 4:23 am
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
May 13, 2008 at 5:34 am
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.
May 13, 2008 at 5:38 am
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
May 13, 2008 at 6:23 am
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.”
May 13, 2008 at 6:55 am
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 🙂
May 13, 2008 at 6:56 am
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.
May 13, 2008 at 7:00 am
Maybe we can summarise by saying that integrated CLR is the perfect solution...
but that we are still looking for the problem 😛
May 13, 2008 at 7:11 am
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
Change is inevitable... Change for the better is not.
May 13, 2008 at 7:13 am
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.
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply