September 15, 2008 at 7:37 am
jay holovacs (9/15/2008)
This is such a silly turf war....
The right tool for the right job.
When the only tool you have is a hammer everything looks like a nail...
September 15, 2008 at 7:43 am
When the only tool you have is an axe.....it gets kinda messy......:D
Hiding under a desk from SSIS Implemenation Work :crazy:
September 15, 2008 at 7:46 am
jay holovacs (9/15/2008)
This is such a silly turf war.I spent a number of years doing C++ (and loved it). TSQL and C# are very different tools and they solve very different types of problems.
To use procedural techniques on set operations is stupid. Looping through operations that should be handled by the database engine is a waste of time.
To use SQL within complex procedural logic is just as foolish. With more and more complex business logic in systems, writing procedural functions, triggers etc. in a language whose procedural functionality is not much beyone GW Basic (primitive user defined structures, no inheritance, no object specific methods, no real polymorphing) is equally foolish.
The right tool for the right job.
It is silly.
The only thing I can say is, I'm not trying to write front-end code using TSQL (not that I could) but developers sure seem to be desperate to write TSQL using front-end code.
If you don't know how to operate a tool properly, or you think it's just too hard to use for too little reward, you're likely to try to find ways to bypass 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
September 15, 2008 at 8:09 am
I looked into writing CLR stored procedures and aggregate functions a few months back. For me, the problem was not enough integration, actually. What I mean by that is that the C# (or whatever) code you write for your stored procs has to go through T-SQL in order to get or write tabular data. If you could access those objects directly, you could write OO code for the non-set-based operations you needed to do, which would be more efficient. Also, the aggregate function just didn't give enough latitude to write a median, say (this was my original goal). On the other hand, adding RegEx resources is always nice.
I'm sure there's reasons to use CLR, but for me I evaluated the stuff I couldn't do nicely in T-SQL and could not find a better way to do it in CLR, so that was a major disappointment for me.
September 15, 2008 at 8:12 am
Without having much in way of a formalized change management system and in a diverse shop with multiple, yet related, database servers and databases, we have a definite need to control the versioning and migration of stored procedures, triggers, and user functions which is getting out of hand. Though we are still primarily using SQL Server 2000, I have been researching using SQL Server 2005 Projects with Management Studio take on this task, much like we are beginning to do with VSS and Vault source control for our .Net projects. However, when perusing the Microsoft MSDN documention, it pays minimal lip-service to to the basic SQL Server Project organization and then jumps to a ton of articles all related to CLR management.
It seems to me that Microsoft is trying to force the use of CLR created SQL server items and that this change control issue would be the only reason why we would ever consider it.
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
September 15, 2008 at 8:24 am
As was the case with me, I expect that many programmers not initially familiar with T-SQL, who start working on the platform will get exposed and then begin to harness it.
In my early days of working with data, I worked with data natively within a full-fledged object-oriented programming language using Microsoft Visual FoxPro. When I moved to SQL Server, I was concerned that I would have difficulty setting up conditional logic and working with other components and objects using T-SQL.
Since working with T-SQL, I find that I can get the job done and I am very happy with its power and flexibility. I'm a big fan. T-SQL is able to handle conditional logic well - If Then, Case, In, etc.
In cases where the task is very ad-hoc in nature and meant to be executed once or a limited number of times, I find that some of my code ends up to be fairly procedural (executes from the top on down) because I'd rather not break certain pieces out into separate stored procedures or functions for the sake of simplicity. But then I find set-based ways to make the procedural operations more elegant and in the end, don't miss some of the more object-oriented ways of organizing the code.
I think it's telling that Visual FoxPro has evolved to offer more features like T-SQL - more SQL conditional logic extensions. It's a move to harness the power of SQL and get more things done with a single query. Let me go on the record and say that I'm continually amazed at what can get done in a single query.
No doubt some nice solutions and code arrangements will come of CLR as well as some mistakes. Some mistakes will fall under the category of "just because you can doesn't mean you should." A stored procedure is probably best used to complete a discrete task - not encapsulate an entire program or sub-system, for instance.
In the end, more people will learn to leverage SQL. CLR adds more tools to the bag.
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
September 15, 2008 at 8:33 am
I was a VB developer for a long time before I began working with T-SQL (and PL/SQL for that matter), but I've yet to find a need for CLR usage in the system where I currently work. In prior positions, which involved a lot of 2- and 3-tier data access applications, there was a large degree of integration between the VB UI, the business objects (where applicable), and the database objects, and that worked well; but everything in my present system is pretty much driven by SSIS packages which in turn launch stored procedures. There is no user-facing interface, so there has been no real need to bring other languages into play, with the exception of a few VB scripts in SSIS.
September 15, 2008 at 8:42 am
Ron Kunce (9/15/2008)
Without having much in way of a formalized change management system and in a diverse shop with multiple, yet related, database servers and databases, we have a definite need to control the versioning and migration of stored procedures, triggers, and user functions which is getting out of hand. Though we are still primarily using SQL Server 2000, I have been researching using SQL Server 2005 Projects with Management Studio take on this task, much like we are beginning to do with VSS and Vault source control for our .Net projects. However, when perusing the Microsoft MSDN documention, it pays minimal lip-service to to the basic SQL Server Project organization and then jumps to a ton of articles all related to CLR management.It seems to me that Microsoft is trying to force the use of CLR created SQL server items and that this change control issue would be the only reason why we would ever consider it.
Acutally - you might care to look at version control through VSTE for Data Developers. The project structure is the issue, not so much what tool you use to develop. DataDude provides a fairly decent platform for established version control and change management on your SQL objects which doesn't require a LOT of manual labor.
As to the bigger issue - I do find it funny that the "DB hating" developers try to beat you over the head with "how things should be done" when they can't be bothered to read up on the "best practices for CLR" in a SQL Server environment.
The CLR in SQL does have some rather interesting (read astonishing and remarkably frustrating) limitations. Why they decided to put the 8K limitation, or force the memory management out of process (so that CLR object can run out of environment resources for not justifiable reason), I just plain can't justify in my mind. And, in a lot of areas, the documentation just plain stinks.
It could be really interesting if fully implemented, but I think they've crippled it pretty harshly, limiting greatly the scope of its usefulness. And I have used it quite a bit, if nothing else to test out and find what (if anything) is useable. I will say a lot more pieces of CLR replacement code never got out of the testing phase than did. As of right now - What's been implemented is limited to regex/string manipulations and (possibly) some odd math functions.
----------------------------------------------------------------------------------
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 9:22 am
As more time passes and the c# and VB.NET application models further mature and the proper architecture is understood you will see more use of CLR or like products inside of new systems.
For true integration and reuse of source code, routines written in C# in our case or VB.Net in others on the application side will migrate into like applications on the database side stashed inside the CLR routines.
Often when we see solutions to problems we see the code we are use to and in this case SQL will win. However, once many of the solutions are done in .net and we do not want to maintain a .net and sql version, we sill see the .net CLR winning. SQL does not run on the client, or on the application only on the SQL Server. .net is being setup to do both.
It will take some time, but it is on the way.
Miles...
Not all gray hairs are Dinosaurs!
September 15, 2008 at 10:11 am
Grant Fritchey (9/15/2008)
One thing I'm aware of in our organization, and I think it's universal. Object oriented developers loathe TSQL. I've watched blood pressures visibly rise in meetings when the discussion comes around to the database (and no, it's not because of me... well, all the time) and how to retrieve data from it. They want to be able to use their coding languages to directly access and control the database and they intensely hate suggestions otherwise. One of our biggest and newest undertakings (which is to finish a project that's failed for the last four years due to bad management and bad design) is going to use nHibernate because the manager and lead developer (same guys that have failed for four years) on the project absolutely despise TSQL. They found a way around it and they're singing to the sun (despite not having written a single line of code, let alone deployed any to production or maintained it over time) that they've solved the "problem" with databases.I honestly expect this trend to speed up and grow.
I also expect more badly performing databases to be out there. Consultants take note.
Must be the same "sun" our guys are singing to. I talked with a couple of the more senior C# developers at this new job of mine... the word "T-SQL" brings on a coughing/choking effect on all of them. They, too, praise nHibernate. I can understand it's use for RAD on simple GUI requests, but now some of them are actually taking to it for ETL. I think the bottom of that boat will rapidly rot out in the face of any scalability/performance requirements just like Java/Hibernate did at my old job.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 15, 2008 at 10:18 am
Jeff Moden (9/15/2008)
Grant Fritchey (9/15/2008)
One thing I'm aware of in our organization, and I think it's universal. Object oriented developers loathe TSQL. I've watched blood pressures visibly rise in meetings when the discussion comes around to the database (and no, it's not because of me... well, all the time) and how to retrieve data from it. They want to be able to use their coding languages to directly access and control the database and they intensely hate suggestions otherwise. One of our biggest and newest undertakings (which is to finish a project that's failed for the last four years due to bad management and bad design) is going to use nHibernate because the manager and lead developer (same guys that have failed for four years) on the project absolutely despise TSQL. They found a way around it and they're singing to the sun (despite not having written a single line of code, let alone deployed any to production or maintained it over time) that they've solved the "problem" with databases.I honestly expect this trend to speed up and grow.
I also expect more badly performing databases to be out there. Consultants take note.
Must be the same "sun" our guys are singing to. I talked with a couple of the more senior C# developers at this new job of mine... the word "T-SQL" brings on a coughing/choking effect on all of them. They, too, praise nHibernate. I can understand it's use for RAD on simple GUI requests, but now some of them are actually taking to it for ETL. I think the bottom of that boat will rapidly rot out in the face of any scalability/performance requirements just like Java/Hibernate did at my old job.
Hmm.... that got me in trouble a few weeks ago... Upon hearing that kind of suggestion, I replied that perhaps we were then using the wrong storage medium for our data. Since they were recommending that everything be done through one of these "ET-Hell" solutions (which of course translates into buckets of cursors, RBAR, etc...), we'd be much better suited with that new-fangled, high-speed way to access data in a serial fashion.... VSAM, I think they call it.....:D
All right, so that wasn't the most subtle thing to say with the VP in the room....:hehe:
----------------------------------------------------------------------------------
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 10:21 am
I see that some folks think of the CLR vs T-SQL as a turf war. Gosh, I wish people wouldn't think that. I do wish they'd do some performance and scalability testing before they dig in on the CLR side of the house. And, contrary to popular believe, T-SQL can be very fast at certain things that require "procedureal" code. Ya just gotta know how to do it right and I think that's the basis of the turf wars on this subject... people will simply take the path of least resistance to get a job done.
I haven't been able to find all of the posts that Matt Miller and myself posted to, be we did a huge amount of testing and about the only place where CLR's came close to beating T-SQL for performance was in the area of RegEx replace. Like someone earlier said, ya gotta use the right tool for the right thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 15, 2008 at 10:26 am
Jeff Moden (9/15/2008)
Must be the same "sun" our guys are singing to. I talked with a couple of the more senior C# developers at this new job of mine... the word "T-SQL" brings on a coughing/choking effect on all of them. They, too, praise nHibernate. I can understand it's use for RAD on simple GUI requests, but now some of them are actually taking to it for ETL. I think the bottom of that boat will rapidly rot out in the face of any scalability/performance requirements just like Java/Hibernate did at my old job.
We have our meeting with the CIO on this topic tomorrow. It's a waste of time though because minds are clearly made up. My goal will be to attempt to communicate the cost (you will have to build normalized reporting databases along side the "object" database) and a risk (performance tuning on the database side is pretty much out without code rewrites and redeployments too). If I can just get them to keep this tool within the bounds of one app until it goes to production it'll be a win. My fear is that a few weeks into development it'll look good and we'll have more apps using it. It won't be until they get to production and have to scale and respond to performance bottlenecks that we'll see the issues arise. Scary times.
"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:34 am
Grant Fritchey (9/15/2008)
We have our meeting with the CIO on this topic tomorrow. It's a waste of time though because minds are clearly made up. My goal will be to attempt to communicate the cost (you will have to build normalized reporting databases along side the "object" database) and a risk (performance tuning on the database side is pretty much out without code rewrites and redeployments too). If I can just get them to keep this tool within the bounds of one app until it goes to production it'll be a win. My fear is that a few weeks into development it'll look good and we'll have more apps using it. It won't be until they get to production and have to scale and respond to performance bottlenecks that we'll see the issues arise. Scary times.
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 15, 2008 at 10:35 am
Grant Fritchey (9/15/2008)
Jeff Moden (9/15/2008)
Must be the same "sun" our guys are singing to. I talked with a couple of the more senior C# developers at this new job of mine... the word "T-SQL" brings on a coughing/choking effect on all of them. They, too, praise nHibernate. I can understand it's use for RAD on simple GUI requests, but now some of them are actually taking to it for ETL. I think the bottom of that boat will rapidly rot out in the face of any scalability/performance requirements just like Java/Hibernate did at my old job.We have our meeting with the CIO on this topic tomorrow. It's a waste of time though because minds are clearly made up. My goal will be to attempt to communicate the cost (you will have to build normalized reporting databases along side the "object" database) and a risk (performance tuning on the database side is pretty much out without code rewrites and redeployments too). If I can just get them to keep this tool within the bounds of one app until it goes to production it'll be a win. My fear is that a few weeks into development it'll look good and we'll have more apps using it. It won't be until they get to production and have to scale and respond to performance bottlenecks that we'll see the issues arise. Scary times.
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.
----------------------------------------------------------------------------------
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?
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply