September 15, 2008 at 10:40 am
Jeff Moden (9/15/2008)
Heh... must be a parallel universe... going through the same thing just now. Our guys are writing a bunch of ETL using C# because they think it's faster... I have to be as patient and watchful as a crane and give them the "opportunity to fail" before I take them out for pork chops. Who knows... I could be wrong about this, but I just don't think so.
It's the latest fad. Last year it was SOA. The year before that it was Agile. The year before that it was a framework. All of them, including nHibernate, are good ideas, but for some reason our guys seem to have implementation issues. "If it works on 10% of the app, just think how GREAT it will be on 100%! Woo hoo! Follow me, boys!"
"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
September 15, 2008 at 10:43 am
Matt Miller (9/15/2008)
Make sure to go after the multi-threaded aspect of testing. Meaning load-testing with single threads or single connections won't show some of the real uglies out there (i.e. resource shortages) you might run into in prod. Meaning - if you're trying to emulate 500 users on the system using 20 pooled connections each running CLR processes, make sure that they're all being used in the test. Just like sometimes having to limit parallelism in exec plans to yield faster execs, you will likely see the perf nosedive with many threads all trying to hammer on CLR code.
Jeff and I are talking about ORM tools, not CLR, but your basic approach, testing, multi-threaded, is absolutely just as applicable. Believe me, we're already planning for it. Unfortunately we have to wait for them to develop way too much of their app before we can start to suggest that maybe, some parts, aren't quite working correctly. It'll be insulting their baby, but, you know, it's not supposed to have a third eye. Sorry, but there it is.
"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
September 15, 2008 at 2:54 pm
Phil Factor (9/15/2008)
...We got stuck, though, because we couldn't find a good genuine requirement for a CLR routine. We still can't.
We used CLR integration when we were developing an app where we needed to be able to show pricing data and inventory quantities-on-hand from dissimilar systems behind very different firewall and security configurations. The only way the IT powers-that-be for this client would allow us access to all the data we needed, was by way of web services.
The size of the data being queried is large enough that it made sense, from a performance standpoint, to let SQL Server handle the data searching, filtering and paging, instead of pulling the billions of rows into memory in the .Net web app, then having .Net handle the searching, filtering and paging. That meant we had to find a way to access web services within the procedural logic in SQL Server. That left using the CLR integration. We could build an easy .Net app to retrieve web service data and then collate that into the resultset returned from the SQL query.
So, we built a stored procedure which queries the locally-stored SQL Server information, while simultaneously calling our CLR object. The CLR object goes out to three different web services to retrieve price and inventory quantities at the various warehouses for those products returned from the filtered, sorted, and paged sql resultset. The web service data is brought back in xml format, parsed, and the CLR object collates this data into the search results being displayed to the user. The CLR also gracefully handles web service outtages, and emails the sys admins with any errors and outtage information (It is notoriously difficult to effectively trap ALL errors of ALL severities in a stored procedure, so this is another area where the CLR has a leg up).
I cant think of any easy way to build a similarly robust system using T-SQL alone. In this case, the CLR saved our butts.
September 15, 2008 at 4:18 pm
Grant Fritchey (9/15/2008)
Matt Miller (9/15/2008)
Make sure to go after the multi-threaded aspect of testing. Meaning load-testing with single threads or single connections won't show some of the real uglies out there (i.e. resource shortages) you might run into in prod. Meaning - if you're trying to emulate 500 users on the system using 20 pooled connections each running CLR processes, make sure that they're all being used in the test. Just like sometimes having to limit parallelism in exec plans to yield faster execs, you will likely see the perf nosedive with many threads all trying to hammer on CLR code.Jeff and I are talking about ORM tools, not CLR, but your basic approach, testing, multi-threaded, is absolutely just as applicable. Believe me, we're already planning for it. Unfortunately we have to wait for them to develop way too much of their app before we can start to suggest that maybe, some parts, aren't quite working correctly. It'll be insulting their baby, but, you know, it's not supposed to have a third eye. Sorry, but there it is.
The CLR is something I oculd speak from experience, and I am suspecting the same thing as you as to the ETL ORM tools (having had a sneak peak at some of the crap it's putting out). I am arming myself for battle on that one as well, primary just gathering perf data for benchmarking purposes....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 15, 2008 at 5:38 pm
{said in the same tone that Homer Simpson says "Doooonuuttts"}
Mmmmmmm.... Pork Chops... 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
September 15, 2008 at 5:40 pm
I'm currently doing the job interview rounds. One of the places I interviewed at is doing the exact opposite. Everything is in T-SQL except the very front presentation layer (which is in Power Builder). They have started using CLR for some of their app but not much. There are a few other places "out there" using CLR for various and sundry activities but it's definitely not widespread.
Hey Jeff - Just wanted to say I've been using your RBAR acronym in my interviews. It really gets the point across.
September 15, 2008 at 6:17 pm
Pam Brisjar (9/15/2008)
Hey Jeff - Just wanted to say I've been using your RBAR acronym in my interviews. It really gets the point across.
Glad it helps, Pam. Thanks for the feedback on that and CLRs!
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2008 at 7:22 am
The article was surprising in that just three of the developers knew T-SQL. I suppose that the other developers had specialties in statistics and finance.
My experience is in software development, though I have designed tables and relations. I think that most shops that depend on databases require developers to know the primary programming language and also T-SQL.
In my current position, the primary database is DB/2, and there is not much use of stored procedures. My employer is also moving away from Delphi to C#. There is some use of Microsoft SQL Server. But all of my coworkers, besides knowing Delphi and/or C#, also know T-SQL.
In a prior position, the database was SQL Server and there were tons of stored procedures for the project I was working on, 200 to 300 stored procedures and the developers were adept at modifying and developing stored procedures. I don't know when they will move to SQL Server 2005.
I have not investigated using CLR in SQL Server 2005; but I agree with other posters that having RegEx in SQL Server would be very powerful. Also, databases are specialty engines; they exist primarily to store and retrieve data. I haven't used CLR in programming project for home use, just stored procedures, for my Call Logging application and Powerball application.
September 16, 2008 at 7:53 am
Why would you want to move away from Delphi to C#, its B*stard son?
You can get Delphi .Net you know.
Pascal is one of the most elegant languages ever designed for programming.
The Delphi implementation of it is superb - kicks seven bells out of MS programming languages in my not so humble opinion. 😛
Hiding under a desk from SSIS Implemenation Work :crazy:
September 16, 2008 at 9:52 am
Shaun McGuile (9/16/2008)
Why would you want to move away from Delphi to C#, its B*stard son?You can get Delphi .Net you know.
Pascal is one of the most elegant languages ever designed for programming.
The Delphi implementation of it is superb - kicks seven bells out of MS programming languages in my not so humble opinion. 😛
The decision was made to switch to C# because that was during the time when Borland didn't know what it was going to do with its languages. The agency was getting assurances that Delphi would continue to be supported.
Me? I'm happy they made the switch to C#. I have years of experience in C++ and was anxious to jump into .Net.
September 16, 2008 at 10:01 am
Ralph Hightower (9/16/2008)
...during the time when Borland didn't know what it was going to do...
Thats always! 😀
Borland had no idea that racoons had eaten all their pond fish, and were still spending $10,000s of dollars a year on fish food.....Management in motion! 😀
C# was written by the guy who wrote Delphi, bonus! 😀
Don't get me wrong, I do like C#, just not the way it was like going backwards when it came to losing the delphi toolset I was used to 10 years ago - VS has just about caught up in the IDE with VS2008.
Hiding under a desk from SSIS Implemenation Work :crazy:
September 16, 2008 at 10:41 am
Oh man you guys are bringing back fond memories. I used to be a Delphi developer. I made the switch over to a DBA focus during the time Borland was waffling about going .Net or doing something else with Delphi. I didn't want to get into any other language / IDE. I had played some with VB .Net and C# and a few others and just didn't like them. So I decided to get out of application development and into DB development / administration.
Oddly enough, I'm finding Delphi coming back into conversations these days.
September 16, 2008 at 11:02 am
Shaun McGuile (9/16/2008)
Borland had no idea that racoons had eaten all their pond fish, and were still spending $10,000s of dollars a year on fish food.....Management in motion! 😀
C# was written by the guy who wrote Delphi, bonus! 😀
Don't get me wrong, I do like C#, just not the way it was like going backwards when it came to losing the delphi toolset I was used to 10 years ago - VS has just about caught up in the IDE with VS2008.
That's a hoot about Borland supplying fish for the raccoons!
Though I haven't programmed in Delphi, I used to program with Borland's C++ and yes, they did have a superior IDE compared to Microsoft. Borland's OWL (Object Windows Library) was also superior to Microsoft's MFC (Microsoft Foundation Class). But I haven't updated any of the Borland products that I own personally to the latest version. Too much stuff that I want and not enough money...
September 16, 2008 at 12:37 pm
I have a CLR proc that clears the files out of a directory prior to another proc writing files to that directory. I have a CLR function that calls an external DLL and gets return data from it.
In the first case, it was either that or turn on xp_cmdshell on a production server, and I didn't want to do that for security reasons.
In the second case, it was that or sp_OA..., and the CLR performs much better and does better clean-up.
I have another one that will be converted from sp_OA... to CLR as soon as I can get around to it.
Outside of file system manipulation without xp_cmdshell, calling external DLLs more efficiently, and calling web services in a proc, I haven't found a good use for CLR. For those, it has been useful. Certainly not critical, but certainly better than other options.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 16, 2008 at 12:40 pm
Interesting! I cut my teeth using Paradox and ObjectPAL. In fact, I had always considered ObjectPAL one of the best languages ever. Oracle had tried to purchase Paradox from Borland just to get there hands on ObjectPAL, but at the time Paradox was still one of Borland's biggest cash cows and Borland turned them down cold. So, Oracle went on to develop PL.
I have dabbled in Delphi and used a host of other languages, and I can say I prefer C# over most others for one reason, code proliferation. Almost every site you visit using Microsoft application list the source in C# nowadays. A truly remarkable feat considering that C# hasn't been around nearly as long as the other languages. It is quickly becoming the COBOL equivalent for OOP programming (along with J2EE Java - but that's another world). If you are like me, and thousands of others, and construct code from samples of how other people have already best resolved the issue you are currently working, then C# is best because it is so much easier to find example of what your trying to accomplish.
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply