October 17, 2007 at 8:15 am
Derek Karpinski (10/17/2007)
You can't have memory leaks in pure managed code. But you can if you involve components that have unmanaged components.
you can certainly have resource leaks in managed code. any time you use an IDisposable object and don't call Dispose, you have the potential for a resource leak.
---------------------------------------
elsasoft.org
October 17, 2007 at 8:36 am
Heh... like I said, Derek... CLR's are for people who don't really know T-SQL... 😉
If you have a GUI involved, that type of formatting should be done in the GUI... not in SQL. Any of the DBA's that you would be so quick to dismiss knows that...
If a GUI is not involved, then you need a refresher course on how to do your time formatting in a very easy manner in T-SQL... and, the bonus is, if you do it right (sorry, oxymoron there 😛 ), you won't have any of the precious "debugging time" you're talking about. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2007 at 8:48 am
OK, fair one..
In the real world though it's too much to ask that Idisosable is called every time....
Humans make errors (and I'm culpable too)
So, to take an example, you've just taken over at least half a million lines of c#,with in excess of 800 stored procs, using at least three third party components.
Now- you've got pretty robust eror handling in the app.
Out of 10 k users you note that you are getting out of memory exceptions for about 5 or six heavy duty users.
Now, show me how you get a memory leak in managed C# code... I'll buy you several beers at the joint of your choice if you can post code that shows memory leak in c# without thid pary stuff or pinvoke..
October 17, 2007 at 9:09 am
Point is that those precious DBA's are going to have to shift to the real world sometime soon..
I'm a DBA and a Developer... Accent on Developer.. 34 years experience ss a programmer. Many of my colleagues have more..
Yes, I'm comfortable in T-SQL. And have done the paradigm shift to set based operations.
Yes, Sprocs can be optimized (but can require regular updates for stored execution plan)
Yes, I'll always go with a sproc.. sometimes with the recompile optoin.
Do it on the server rather than on the client
And CLR Integration giives me more power to do what I need.
At the end of the day it's all about data, We can use many tools. We have to make the right choice
October 17, 2007 at 10:23 am
Derek Karpinski (10/17/2007)
OK, fair one..In the real world though it's too much to ask that Idisosable is called every time....
I understand it's a pain to have to call Dispose, but if you don't, you can get into trouble. If your code is running on a server, mistakes like that can cause big problems.
The point is that objects that implement IDisposable have hooks into native resources such as file and registry handles, GDI objects, etc, and if you don't clean them up, you may leak them. This is because GC does not track such resources - it only tracks memory. So if the handle count starts to skyrocket because you are not calling Dispose on someone that creates handles, GC won't even notice unless it's accompanied by memory pressure. so GC.Collect() isn't invoked, and you run out of handles. boom.
You might benefit from reading this:
http://www.bluebytesoftware.com/blog/PermaLink.aspx?guid=88e62cdf-5919-4ac7-bc33-20c06ae539ae
---------------------------------------
elsasoft.org
October 17, 2007 at 10:44 am
A real quick and easy way to make a split funciton, that returns records as a table.
Imagine writing that in TSQL???
October 17, 2007 at 4:05 pm
Derek Karpinski (10/17/2007)
Point is that those precious DBA's are going to have to shift to the real world sometime soon..I'm a DBA and a Developer... Accent on Developer.. 34 years experience ss a programmer. Many of my colleagues have more..
Yes, I'm comfortable in T-SQL. And have done the paradigm shift to set based operations.
Yes, Sprocs can be optimized (but can require regular updates for stored execution plan)
Yes, I'll always go with a sproc.. sometimes with the recompile optoin.
Do it on the server rather than on the client
And CLR Integration giives me more power to do what I need.
At the end of the day it's all about data, We can use many tools. We have to make the right choice
What power? You said yourself the only thing you've used it for is to format a date.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2007 at 4:20 pm
mdiaz (10/17/2007)
A real quick and easy way to make a split funciton, that returns records as a table.Imagine writing that in TSQL???
Split "functions" are nothing more than RBAR... and the need for a split function that returns records as a table may indicate a design problem to begin with unles it's for a call to a proc using a delimited parameter.
For batch imports of CSV's or other delimited records, you would use BULK INSERT, BCP, or (yech) DTS instead...
For single record GUI stuff, creating the necessary User Defined Function is easy in T-SQL... and there are multiple simple high-speed methods depending on whether you want to use a Tally table or a While loop.
And, if you've made the design error of having a delimited column in a table, the Tally table method will let you split the entire column into a single result set in no time flat.
Like I said... CLR's are for people who really don't know T-SQL and have to resort to outside code. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2007 at 4:35 pm
It was actually for a paramter in a previous project where I was getting a list of custid's comma dilimited. When I attempted to write in Tsql I had to create a last_coma_found int and make an if,elseif,else scenerio (if -for the first found, else for inbetween, and else for that comma that just happened, and run to the end of the list) and encapsulate the entire thing in a sqlfunction to actually return back a @Table of ints. I then had to later modify it when our custid's became varchar(10) instead of ints. Oh how sweet it was when I created a CLR.
AND (i know i am not supposed to start a sentence with an and) Since you pulled my string, i have YET another reason for a CLR. Recursive code. Consider the following scenario (funny because it came up today), an audit trail where you have a previous Id and a current id , and the next record has to join on the previous to the next of the next.. (as a real-life example) began in one department, and ended up in another, and yet another .. and so for.
So now a recursive function is the easiest method. Otherwise your statement will begin to be a select statement where in (another statement) if not, then return the list of id's you collected. Perhaps a little dynamic sql might do the trick, until you run out of of space on a varchar(8000).. ouch! 😛
October 17, 2007 at 5:07 pm
So... you've never seen recursive CTE's? You just don't need to use a CLR for such a thing... the tools are in T-SQL... you just need to learn to use them. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2007 at 1:59 am
The point is that any tool that enhances productivity and promotes easily understandable code is the way forward.
CLR integration gives us programmers a very useful tool. Sure, if you can do it in T-SQL quickly in a way that mere mortals can understand go for it. And as I've said before if you can use a set based operation that's great. Unfortunately, I've had to resort to cursors in the past... Now, they are supported in T-SQL and they are very much my last resort..
So if I can write something quickly in C# that my team members understand or slowly in t-SQL using arcane statements, requiring the sacrifice of a goat which way should I go?
I had to look at a sproc recently... legacy code.. Only 400 or so lines... Shared it with my team - they couldn't understand how it worked. We proved it didn't work for several cases.
Rewrote it in t-SQL.. Took me two days... now it's supportable, testable and works. a mere 800 lines. But it would have taken less than a quarter of the time in c#
So, the sanctity of the data is important. But we have a duty to provide quick access to that data. Usually, T-SQL is the resource of first choice, but CLR will be the future.
October 18, 2007 at 6:55 am
CTE are nice, haven't used as much as I like to, but nevertheless they bring a nice feature forward to 2005. Just found a good article on CTE's and recursive tSQL, thanks! In a way they look like the with statement used in VB. But as a vb,c# programmer first then a sql-guy, I feel more comfterable in the programming domain over the old tsql. And if we believe Microsoft's commnets on how CLR's are actually more memory efficient, eventhough its microsoft vs microsoft then I suppose CLR's do give us programmers an advantage. I suppose thier alternate strategy is to fuse together 2 worlds at the lowest level possible, the binary level.
October 18, 2007 at 7:07 am
Point is that those precious DBA's are going to have to shift to the real world sometime soon..
I'm a DBA and a Developer... Accent on Developer.. 34 years experience ss a programmer. Many of my colleagues have more..
Yes, I'm comfortable in T-SQL. And have done the paradigm shift to set based operations.
Yes, Sprocs can be optimized (but can require regular updates for stored execution plan)
Yes, I'll always go with a sproc.. sometimes with the recompile optoin.
Do it on the server rather than on the client
And CLR Integration giives me more power to do what I need.
At the end of the day it's all about data, We can use many tools. We have to make the right choice
Derek - i completely agree with you.
I can understand the DBA point of view, but feel that the them and us relationship that seems to be put across here isn't helpful. Surely developers and DBA's need to communicate to each other. If you as a DBA think things can be done in a better way surely that has to be communicated to the developer? I understand that CLR sp's are pre-compiled and can't be viewed in the database. Surely in that case the code needs to be supplied so you know what is going on. A lot of the DBA's i've worked with have been very secretive, not very willing to share their thoughts with the developers. I'm not saying anyone on here is like that, but to make things run as efficiently that would definately make things better.
As far as my position is and what I have used CLR for - converting some code from using cursors i couldn't see another way of doing this. Some of this (probably quite a lot in fact) i put down to the way the system was designed (in which i wasn't involved and just had to deal with as i wasn't given the time to change this). It involved dynamic code and string creation to pull variables from report definition tables and create sum's from these variables (linked to selected geographies passed in and coming from other tables) in the data tables depending on certain values in certain columns. I couldn't see how to do this in a set based way. As I said before i inherited the system and since the software written required certain resultsets back there was no way around this. In an ideal scenario i would have liked to totally taken the structures apart and redesigned the database in a more appropriate way or even using different technologies, but since that wasn't an option i had to deal with what i had.
October 18, 2007 at 7:34 am
Whoa. Damn right Matty. I've been a DBA and tried to advise my developers. I am a developer right now and currently fight my orgs dbas. Who are less certificated than me..And as I've been programming for more than 34 years I suspect are less experienced.. My problem.
But, and I will reiterate this, Developers provide solutions.And thats where the money comes from... A good DBA can help. But at the end of the day if you do not help the dev you are a problem. And should be terminated. Brutal, yes, but practical.
And I speak from both sides of the divide.
We have to provide an accurate view of the data. We have to ensure that the data is consistent and correct.
And we have a duty to use the best available tools - leading edge rather than bleeding edge
Rant over, out to you
October 18, 2007 at 7:46 am
... and, we have to help "application developers" learn how to program in T-SQL 😉 Although CLR's are helpful for those that don't really know T-SQL or set based programming, folks really need to learn T-SQL if they want their application code to have any chance at performance and scalability. :w00t:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 46 through 60 (of 71 total)
You must be logged in to reply to this topic. Login to reply