programming and human factors
by Jeff Atwood
It's intended as sarcasm, but I believe this Daily WTF entry on Stored Procedures should be taken at face value:
I'm sure we've all heard, over and over, that inline SQL is generally a bad practice, and that we should use Stored Procedures when possible. But let's be realistic for a minute. Who wants to write a stupid stored procedure for every stupid little simple query needed.
Have you ever worked on a system where someone decreed* that all database calls must be Stored Procedures, and SQL is strictly verboten? I have, and this decision leads to incredible development pain:
And many people buy into this philosophy, lock stock and barrel:
At just about every talk I give I always try to make several consistent statements. One of which is: ‘Whenever possible use stored procedures to access your data'.
However, there's one small problem: none of these things are true in practice. The benefits are marginal, but the pain is substantial. And I'm not the only person that feels this way. John Lam also adds:
As a guy who dabbles in low-level bit twiddling stuff from time-to-time, the performance claims are quite interesting to me. The new (as of SQL Server 7.0) cached execution plan optimization in SQL Server looks to me a lot like JIT compilation. If this is, in fact, the case it seems to me that the only overhead that would be associated with dynamic SQL would be:
I can imagine quite a few scenarios where the above overhead would disappear into the noise of the network roundtrip. What upsets me are the folks who spout forth anecdotal arguments that claim stored procedures have "much better" performance than dynamic SQL.
- The amount of bandwidth + time it takes to transmit the dynamic SQL text to the database.
- The amount of time it takes to calculate the hash of the dynamic SQL text to look up the cached execution plan.
For modern databases and real world usage scenarios, I believe a Stored Procedure architecture has serious downsides and little practical benefit. Stored Procedures should be considered database assembly language: for use in only the most performance critical situations. There are plenty of ways to design a solid, high performing data access layer without resorting to Stored Procedures; you'll realize a lot of benefits if you stick with parameterized SQL and a single coherent development environment.
* "the man".
Having worked with both Oracle and SQL recently, and having seen dynamic SQL being used in both, I know that performance is definitely an issue, but SQL server developers probably understand the performance implications less than their Oracle developer counterparts. In Oracle, not using bind variables is one of the big no-no's because of SQL parsing and SQL statement caching, etc.
Dynamic SQL is definitely necessary in some cases, but where you can, you should definitely use parameterized SQL or stored procs. Stored procs are generally precompiled so you know that, as it is created in the database, that the statements are valid, so you don't have SQL syntax errors. Knowing these things should save you headaches later on. Jeff, you comment on the lack of ability to debug stored procs in the same environment as the rest of the development that you do, but I would argue that if you used stored procs, you are less likely to have to debug the SQL. Not only that, if you had stored procs, it's unit-testable.
I personally don't like to have a lot of business logic on the database side either, but I think the logic relating to data integrity, etc. and having to do with data storage must definitely be on the database side. For example, and I think most people will agree with me, if you have a data table and then you have a hist table to keep track of changes to rows in the data table, that "business logic" should stay on the database. For me, I personally like to keep the database access to storage and retrieval and move most of the business logic out of the database into a middle tier. But this really depends on the project requirements.
The project I am currently working on is extremely heavy with database-side business logic, but also has a lot of logic in the web code (ASP.NET). This is because the database is actually owned by our business unit. We have separate database developers who work on the database and the data is imported nightly from different locations. We use stored procedures almost exclusively, on SQL server, and we create dynamic SQL in the stored proc. and - get this, we pass XML in order to get around the limitation on complex data structures. So not only does the stored proc have to parse XML, it also has to create dynamic SQL string and execute it. In our case, I think it makes sense given the functional requirements.
Wen Lai on October 29, 2004 2:19 AMSQL Server 2005 to the rescue then, eh?
CLR? Check
Stored Procedures have the ability to be created in a .NET language like C#, VB.NET, etc? Check
Sold? Check
Lets use a hypothetical real-world scenario to understand why stored procedures are not always the best approach:
You have 3 database platforms on 3 servers. One server in Washington runs Windows 2003 with SQL Server 2005. One server runs Oracle on Linux or a Sun (sorry don't use Oracle to know if they need some outrageous beast OS/Hardware). One server runs MySQL on a Linux box.
All 3 databases share a table and must synchronize that table. You develop an application that checks each table on each database platform to make sure the synchronization is working right. Would you rather use .NET and let the Data Providers handle all of the SQL/Oracle/MySQL work? Or would you rather have to know and access 3 different stored procedures, knowing each callback and database platform UP FRONT.
I'd use .NET with 3 data providers and using common code that generates the SQL needed to use all 3. This way you don't have to code around any inconsistencies in the underlying databases because they will all return the same .NET structures you can easily use. Can you honestly say you can use the same stored procedure and make it work exactly alike on every database platform you put it on? Simple stored procedures maybe, but they're so different that you're bound to have some kind of differences that you'll have to code around.
Then again I could just be pulling all of this out of my ass. I've never really bought into stored procedures because I want apps that work when you plugin a new data provider. I don't want to have to think "Oh you have to add this stored procedure, throw salt behind your back, cluck like a chicken 3 times, and pray that it happens to work exactly as it should".
I know it's not quite like that but I like having all of the SQL in one place. I just edit my code where the code is, not having to remember to edit both the code and the stored procedure on whatever database I happened to be working on at the time. Oracle stored procedures can't be accessed in VS.net by default, and I'm way to cheap to spend some outrageous amount of money so that I could edit all stored procedures in the IDE I'm doing my application in. SQL Server 2005 may change my thinking about all of this, but for now I'm just not seeing how stored procedures will benefit me personally. If I were dealing with customer databases and their own DBA BOFH then I may be forced to use stored procedures but I could slap the DBA whenever the procedure wasn't working right. It does add an extra layer of slappage not currently present in my workflow.
Jeremy Brayton on October 29, 2004 2:36 AMYes, yes yes! You hear these arguments all the time, and I've never really bought into them. Using stored procedures in an app basically means that you've a) added another layer, b) added another language and c) lost all database portablility. At the very least. I don't think I've ever heard anyone go into the details around the ambiguity of SQL error messages and debugging problems before, though (kudos).
In my mind, they're just not worth the added effort in maintenance, unless you encounter one of those situations where you can *actually* get a real, calculable performance gain from them. Otherwise, I'm all for KISS. Use a good OR Mapper instead.
Jeff Perrin on October 29, 2004 3:09 AMI am all for abstractions and separations of logic. I definately do not believe that T/SQL or PL/SQL are expressive enough languages to describe that logic. Sticking critically meaningful code in the DB from what I've always encountered leads to the Clusterous Fucokous Anti-pattern.
This is true regarless of where you put it. Procs or embedded.
Hurray for ORM.
sam on October 29, 2004 3:16 AMhear hear! The last big project I did I mixed the usage of Stored Procedures and dynamic SQL, and the result is absolutely great.
You get the performance or adaptability where you need it (it was for an ISV) and can keep the maintenance to a minimum (we even supported both SQL Server and Oracle).
Besides the performance aspect, adaptability was our main reason for using SPs at all, because you can tweak the client's configuration just a bit more using out-of-application SQL statements ;-)
Dick Appel on October 29, 2004 5:58 AMbut where you can, you should definitely use parameterized SQL or stored procs
Parameterized SQL should ALWAYS be favored over dynamic SQL. Parameterization prevents SQL injection and generates more generic (and therefore more cachable) execution plans.
Jeff, you comment on the lack of ability to debug stored procs in the same environment as the rest of the development that you do, but I would argue that if you used stored procs, you are less likely to have to debug the SQL
Not in my experience. We still need to look at the tables and understand the data to do our work; having SQL helps us do that. Procs are totally opaque.
but I think the logic relating to data integrity, etc. and having to do with data storage must definitely be on the database side.
Definitely. The database should be relationally coherent and have a clean, understandable design. Fundamentally bad db design shouldn't be able to hide behind a bunch of stored procs. The data should be good at.. being coherent data. Not being a "customer".
We use stored procedures almost exclusively, on SQL server, and we create dynamic SQL in the stored proc. and - get this, we pass XML in order to get around the limitation on complex data structures. So not only does the stored proc have to parse XML, it also has to create dynamic SQL string and execute it. In our case, I think it makes sense given the functional requirements
Urgh, I dunno, that sounds like a lot of extra conversion work. If you're going to do that much work, why not do a little more and write a web service API. Then you could pass true objects over HTTP, and not even care how it is handled on the database side. If you are writing stored procs that themselves build dynamic SQL, might as well cut out the middleman and move this to a web service API that generates dynamic sql. It seems your goal is abstraction, not performance, and stored procs are a terribly leaky place to build your abstraction layer, IMO.
Jeff Atwood on October 29, 2004 7:20 AMPerformance issues aside, the security implications are worth using stored procs. I don't buy the argument of convenience of development or portability - it's just laziness. It is a pain to debug apps when the environments are not integrated. I suggest you use parameterized dynamic SQL during development to get your SQL right and then create the stored proc based on that.
In some cases, when you are not the one who develops both the stored proc. and the calling code, you don't necessarily want or need to know what the stored proc. is doing. If something goes wrong with the app, it should be pretty obvious if the problem is in the stored proc. or in your code. If not, then it's either a problem in the design or the code that you can't figure it out (of course, you are not always going to be looking at your own code or code that's easy to maintain and understand).
Stored procs should be used to perform most database operations because it allows better structure. It enforces it. It's basically the same idea of creating procedures/methods in procedural languages.
The main problems, as noted, are when you want complex data structures (objects) for parameters or variable number of parameters (such as a search function with many criteria).
In larger projects where there are many developers, using stored procs will make sure that it will remain easy for everyone in the team to be able to use and reuse the same stored procs. Also in larger projects, tracking and finding SQL statements in your code can be ugly and error prone.
Basically, I agree that there are some limitations of stored procedures that I wish didn't exist, but overall, I believe stored procs. are much better for the purpose of designing and developing applications and if the performance is even marginally better than dynamic SQL, then it's well worth it.
Stored Procedures are written in big iron database "languages" like PL/SQL (Oracle) or T-SQL (Microsoft).
Stored Procedures typically cannot be debugged in the same IDE you write your UI.
Naked SQL statements clumsily embedded in other languages use the same 'big iron' database languages. But wrapped up as text strings like many programmers do, they're not debuggable at all, in any IDE.
Stored Procedures don't provide much feedback when things go wrong.
Plenty of support for input validation and raising of errors exists. If you choose not to take advantage of it and then complain that it's not being done...
Stored Procedures can't pass objects
Create a class that formats an object's properties as a stored procedure call, then use the class and forget for the rest of yout project's development cycle that either SQL or stored procedures even exist.
Stored Procedures hide business logic.
Yes! N-tier design calls this a feature, not an issue. Your code isn't supposed to know about or depend on the details of the stored procedure implementation. It's just supposed to use the interface provided.
Stored procedures and triggers also allow for input validation, preventing a typographical or design error in other code from dumping erroneous data into the tables.
Stored procedures allow multiple sections of code to call the exact same steps for database interaction, and allows the developer to easily edit those steps and effect all dependent code at once.
Disallowing direct table access and allowing only stored procedures limits what can be done to the data to that fucntionality provided by and validated by sstored procedures. This disallows inappropriate or erroneous usage by authorized users, and severely limits what an unauthorized user can do, since they can't even list the stored procedures that exist or the code behind them.
Even arguments that 'it's too much typing' fail to hold water once you start taking into account having to surround the naked SQL calls with string quotes and line-continuation characters or string concatenation methods, factor in editing duplicated code in each place it occurs and the writing of additional client-side code to handle the input verification that could in most cases be more easily handled by the database server itself.
Eric on October 29, 2004 11:29 AMHi, Jeff: You assert that "none of these things are true in practice," but present no evidence to refute these points:
* Stored procedures can be individually secured within the database. A client can be granted permissions to execute a stored procedure without having any permissions on the underlying tables.
* Stored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component.
These seem to me like valid reasons to use stored procedures.
Also, VS.NET does allow SQL Server stored procedures to be "debugged in the same IDE you write your UI."
Phil Weber on October 29, 2004 12:08 PMI read Jeff's post last night and was going to comment this morning, but Wen and Eric covered pretty much all the points I wanted to. I am of the mindset that coming up with justifications for removing layers of abstraction in development is sheer laziness on the part of the architect; stored procedures are there for a reason (several actually)...not only for performance gains, but to separate business logic from (in what is often the case with web development) the presentation layer.
I'm actually astounded that the first point (re: "so-called languages are archaic") was even mentioned: I don't know about you guys but all the stored procedures I've written are in T-SQL on MS SQL, which is pretty much line for line the same code I would "inline" into the web application code, if I so chose to go that route.
We are in the midst of a 3 month project with another company who is responsible for maintaining a huge Lawson HR system, and transmitting job requistion and hiree data to and from our custom hiring application. Stored Procedures are practically a must in this scenario: Their lawson developer sends us the data and we deal with it as we need, sending it back and forth via stored procedures. I need to have less than a rudimentary understanding of what is going on in Lawson, and quite frankly, it's not my responsibility with this project. I am responsible for handling the data on our end; that's *exactly* why they have a Lawson developer...he has his role and I have mine.
Business logic hidden, data transported, end of story.
Shawn Holmes on October 29, 2004 12:22 PMStored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component.
Maintaining our procs is a nightmare. We have a handful of guys who know PL/SQL and the Toad IDE well enough to actually edit the procs, much less debug a complex one. Things might be easier on the SQL Server side due to the MS integration, but until they get CLR in the engine (which I think is fantastic), it's still Yet Another Layer we have to deal with and keep in sync, etc. This isn't easier to maintain: it's harder!
Stored procedures can be individually secured within the database. A client can be granted permissions to execute a stored procedure without having any permissions on the underlying tables.
Can't you just set up database logins for those users that grant access appropriately?
Of course, as in all things: it depends. You may have a project where the "all stored procs" approach makes sense. What I really object to is this blanket (and INCORRECT) perception that procs are "just better" when my practical experience indicates they are WORSE on every project I've worked on. Even for performance! You need a really *compelling* reason to use procs-- critical performance junctions, remote vendor security lockdown, etc.
Jeff Atwood on October 29, 2004 1:38 PMNaked SQL statements clumsily embedded in other languages use the same 'big iron' database languages. But wrapped up as text strings like many programmers do, they're not debuggable at all, in any IDE.
Regular expressions are another language "clumsily embedded" in my code, yet it makes my life much easier. Go figure.
Yes! N-tier design calls this a feature, not an issue. Your code isn't supposed to know about or depend on the details of the stored procedure implementation. It's just supposed to use the interface provided.
The database layer is not the only place to do this. I would argue it's one of the WORST places to do it because of all the reasons I discussed in my post. There are many many ways to isolate your data access layer. My current feeling is, if you REALLY care about building a reusable, abstracted API, then you need to be building a web service. End of story. That's the only abstraction layer that, IMO, currently means anything. Everything else gives you the *illusion* of abstraction (remoting, stored procs) with few of the benefits. But once the web service is built, all my abstraction is taken care of-- I can call it from Mac, Dos, Unix, whatever, any platform, any language. It's work, but you get REAL abstraction, a huge benefit. And the back end can do procs, parameterized sql, black magic, whatever. Who cares at that point, because it's fully abstracted away.
Jeff Atwood on October 29, 2004 1:46 PMStored procs should be used to perform most database operations because it allows better structure. It enforces it. It's basically the same idea of creating procedures/methods in procedural languages.
But again, are procs the only way to achieve this isolation? I don't think so, and they also have some serious downsides.
Jeff Atwood on October 29, 2004 1:49 PMI couldn't agree more!
JavaKid
JavaKid on November 1, 2004 3:01 AMJeff,
I would agree with you in the case where a database will only be used by a single application being developed by a single programmer for a single customer because all of the available knowledge is in one brain and there is only one skillset to work with. However, as soon as you start adding programs, adding programmers, or adding customers I switch to being firmly in the camp that says "all interaction with the database will be through stored procedures" and here is why:
1. As a rule VB programmers are terribly inefficient T-SQL coders. They are row-based thinkers, not set-based thinkers, and that leads to very slow code. I spend a lot of my time "fixing" code written by VB programmers that "works" but takes forever. I just finished rewriting an app that used to run overnight or over a weekend (depending on the options selected) that now runs in 15 - 20 minutes. The efficiency did not come because of parameterized queries or cached queries, it resulted from using set logic on the server instead of executing hundreds of thousands of queries from the client and processing the logic on the client row by row.
2. Anyone that has had to research a proposed schema change will tell you what a nightmare it is to identify all of the code that could be affected by the proposed change. How do you know if you have found all of the code? Do you just wait and see what breaks after the change is made? Requiring that all interaction with the database be done through stored procedures makes it easy to identify the stored procedures that will be affected.
3. Stored procedures can be designed, written, and tested separately from the VB code. The VB programmer and the database programmer reach an agreement on what the parameters will be and what the output will be and they both write their code in accordance with that "contract". This allows for parallel development and independent testing.
In closing I would like to throw in a couple of other general observations:
1. You probably would not want a contractor to build your house for you if they only used carpenters. It is true that the majority of the work should be done by carpenters, but you would probably want some electricians to do the wiring and some plumbers to handle the pipes and toilets. Does that mean that carpenters couldn't build a house without electricians or plumbers? No. Would you want to buy that house? Probably not.
2. I have found that most problems with performance have their root cause in poor database design. Spending more time upfront thinking through the design and understanding how it will be used will give you more performance gain than parameterized queries vs. dynamic queries.
Paul Lach on November 4, 2004 9:30 AMit resulted from using set logic on the server instead of executing hundreds of thousands of queries from the client and processing the logic on the client row by row
That just sounds like bad coding, pure and simple. *Hundreds of thousands* of queries? How could that ever be a good idea?
Anyone that has had to research a proposed schema change will tell you what a nightmare it is to identify all of the code that could be affected by the proposed change.
An illusion, as we have had table changes which broke dozens of stored procedures. It's the same problem, just on a different (and harder to debug) tier. Reality is, changing the DB will break everyone, which is why I favor total transparency whenever possible.
Stored procedures can be designed, written, and tested separately from the VB code.
So can API layers. Stored procs are not the only form of database abstraction, just one of the more limited ones.
Does that mean that carpenters couldn't build a house without electricians or plumbers? No. Would you want to buy that house? Probably not.
I agree you need developers that understand basic relational database design. I have worked with developers (I am looking at you, Shawn Holmes) who didn't even index their tables.
I have found that most problems with performance have their root cause in poor database design. Spending more time upfront thinking through the design and understanding how it will be used will give you more performance gain than parameterized queries vs. dynamic queries
I agree. One of the advantages of parameterized SQL is that it provides much greater transparency into the database. It's a lot easier to hide a bad database design by sticking it behind a bunch of stored proc "interfaces". The DB schema should ALWAYS be well designed, and the more eyes you have on it, the more likely it is to be well designed. If 4-5 developers can't figure out the crazy queries necessary to do basic business work in your schema, maybe it isn't very good?
Jeff Atwood on November 5, 2004 1:23 AMJeff,
"That just sounds like bad coding, pure and simple. *Hundreds of thousands* of queries? How could that ever be a good idea?"
From the VB programmer's point of view it seemed like a good idea. The purpose of the application was to determine the eligibility of thousands of people for health care benefits based on a complex set of rules based on number of hours worked, hire date, where they worked, when they worked, and other personal information. The programmer executed a series of queries to get the necessary information for one worker for one time period. They then used that information to calculate whether or not that person had earned eligibility and executed an update query to save the eligibility information. They put that block of logic inside 2 loops - one to iterate through all of the workers and one to iterate through all of the time periods for each worker. To the VB programmer that made perfect sense - it was just like using arrays. To a database programmer that is insane, but to a VB programmer it made perfect sense.
"An illusion, as we have had table changes which broke dozens of stored procedures. It's the same problem, just on a different (and harder to debug) tier. Reality is, changing the DB will break everyone, which is why I favor total transparency whenever possible."
You seem to have missed my point. If all of the code referencing a table is contained in stored procedures I can find all of the affected stored procedures by doing one query against the syscomments table (where all stored procedure source code is stored by SQL Server). I do not know of any way that I can find all of the VB code that may exist that references a table because there is not a single known place where all of the source code resides. The issue is not how many pieces of code will be affected, the issue is how do I find all of the affected pieces of code?
"Stored procedures can be designed, written, and tested separately from the VB code.
So can API layers. Stored procs are not the only form of database abstraction, just one of the more limited ones."
Once again I think you missed my point. Whether you execute queries directly from VB or you put them into stored procedures the queries themselves must be tested. I believe that it is better (and easier) to write and test stored procedures separately so that when you are testing and debugging your VB code you have eliminated the dynamic SQL as a possible source of problems.
"It's a lot easier to hide a bad database design by sticking it behind a bunch of stored proc "interfaces". The DB schema should ALWAYS be well designed, and the more eyes you have on it, the more likely it is to be well designed. If 4-5 developers can't figure out the crazy queries necessary to do basic business work in your schema, maybe it isn't very good?"
In discussing the pros and cons of using stored procedures vs. dynamic queries from VB we should both be assuming the presence of skilled programmers on both the VB side and the database side. It is somewhat disingenuous to argue that you need to put the queries in the VB code because, if you don't, the database people will write a bunch of crappy code and stick it in stored procedures that you can't understand and are stuck with. It would be equally wrong for me to argue that VB programmers should be forced to use stored procedures because if they are allowed to write queries against the database they will be very inefficient and bog down the database server or write update queries that screw-up the data. Even though that may be true given the talent levels on your projects, the philosophical argument should assume that both sides have talented people.
The question is, if given equally talented people on both sides, what is the best way to work with a database and why?
Paul Lach on November 5, 2004 9:46 AMThink about accessing the database through stored procedures (only) as a means to using your database like a service. It provides whatever (and only) the functionality implemented by your stored procedures, regardless of what application is accessing it. It you have one application and 4 import (applications) all using the database, stored procedures will help ensure that they all do it in a consistent way. New applications will already have a good baseline of database logic to choose from. You database becomes more like an entity, or single object that has specific functionality available.
Change the word "database" to "web service" and I agree. That's a meaningful layer of abstraction for apps that need a formal API layer (note that not all do).
If you've never tried stored procedures, you owe it to yourself to do so and formulate an opinion based on your experience
Oh, believe me, my opinion is based on extensive use of stored procs in many projects. They are the source of much continuing pain.
Check out the latest proc "submitted by Jeff Atwood" at
http://www.thedailywtf.com/ShowPost.aspx?PostID=29337
When you have worked on a database that has over 2 TB of data, and literally more than 3k tables/views you will understand not only the power, but NECESSITY of them
Sure, YMMV. What I object to most is the argument that stored procs must ALWAYS be used, no matter what, which is a pretty giant net negative.
and the plan analyzer shows that it's taking 19+ seconds trying to figure out what to do with it, you know it's time for an SP. Actually, up-front would have been the time
What database are you using? As mentioned in the post above, parameterized SQL query plans are *automatically* cached by most modern database systems. Fex, in SQL Server as of version 7.0 or greater. So the query plan will only be calculated the first time the query is sent; all subsequent submissions of that same parameterized SQL will be looked up in the query plan cache.
When you have 70+ projects sitting on one giant shared database + schema you simply cannot have developers mucking about directly with tables or views unless ABSOLUTELY necessary
Again, are stored procs the only way to accomplish this goal? If you want an API that really *means* something, I'd put a Web Service on top of that database and disable access from any port other than port 80.
Jeff Atwood on March 16, 2005 5:51 AMEric wrote:
Naked SQL statements clumsily embedded in other languages use the same 'big iron' database languages. But wrapped up as text strings like many programmers do, they're not debuggable at all, in any IDE.
Paul Lach wrote:
2. Anyone that has had to research a proposed schema change will tell you what a nightmare it is to identify all of the code that could be affected by the proposed change. How do you know if you have found all of the code? Do you just wait and see what breaks after the change is made?
The answer to both of these problems is to have unit tests. It's that simple. Once you've got your 'dynamic' SQL passing its tests, you know it's right. Changed your schema and want to know if you found all the affected code? Run your tests.
You know your tests cover the affected code because you either write code test-first (no code is written until a failing test is), and/or you rely on a coverage tool, which tells you which bits of the system have been exercised.
With ADO.NET's generated typed 'DataSet' classes, you even get to see compiler errors showing you where you broke your code when your schema changes. You can just browse your 'task list' and fix each problem in turn. To make this work, all you have to do is regenerate your DataSet classes.
Obviously this is no substitute for a full set of unit tests, but it's useful nonetheless.
Rik
I believe that any DataLayer must be a simple code block, that they allow operations against DB.
That code block would not have to know on the Business Entities. Single to specialize it is to execute the operations (Store Procedures and SQL Sentences) against the engine DB (SQL, Oracle, DB2, etc.), with which this setting.
Finally, I invite to you to download the DataLayer.Primitives Public Version.
This is very cool Data Layer :)
DataLayer.Primitives - Readme!
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=1389
Anyone who says that T/SQL is archaic while still coding in ASP deserves a boot to the head. If you use Cold Fusion, Java, or C#, that's another story.
SP is best for distinct logic building blocks with encapsulated business rules that can be treated by the developer as a black-box object (think OOP advantages).
In-line SQL is best for single queries that may additionally be cached at the app server level if 'static-enough'.
Gene on July 12, 2005 8:20 AMAlmost every ASP.NET project I have seen (where I wasn't the architect ;-) ) the use of stored procedures has led to complete and total chaos. Part of the reason is that they tend to be there because the designers centered everything on the relational database design, making that the first priority, and part of the reason because no one can properly debug reams of bloody t-sql when you dont even know if the bug is in the t-sql or in the c#. In fact, when it comes to basic CRUD (but not complex report type or long running queries in many cases) I dont see any reason at all to use stored procedures and in my very good experience, it is dangerous to allow their use. Where this dogma about avoiding in-line sql has come from I don't know, but it should be recognised as just that - dogma. Security? Performance? Pfa! My arse!
Frank on October 13, 2005 4:31 AMAll this has made me remember one of my biggest wish items for stored procedures: interfaces.
I recently duplicated a large set of procs to provide my code with identical field names in the results they returned, e.g. "name" and "id", vs. "entityname_name" and "entityname_id".
Brady Kelly on December 8, 2005 11:13 AMFirst let me start out by saying I have been developing database application since DOS which I had to migrate many applications to a new platform. I have worked on both stored procedure base systems and embedded SQL applications. Performance was always better with stored procedures.
I personally do not feel the business logic should reside in the application, application have their rules and data has its rules. In today’s IT world we now have many interfaces for the same application. They are thin, rich, and/or smart clients. These clients can all use the same datasets for accessing systems.
Why would you put embedded SQL in every client? That does not sound that productive. I would have to redistribute the client. With a thin client the user would get the changes the next time they access but I still have to move my code from development environment to production. With smart clients I would have to distribute the application too. Can I have smart and thin clients accessing the same datasets? Yes, so why embedded SQL?
One other argument I have seen on the Internet about stored procedures was that it was difficult to diagnose when a problem exists. What I say to these people is you do not understand databases and need to go back to school. If you think it is better to download the data to the client and then process the data for some business rule you are crazy. The whole purpose of DBMS was to return only the records necessary to accomplish a task and save the throughput.
One other point to make about stored procedures; it is easier to manage security with stored procedures. So you see I can not see why any project would fail with stored procedures, unless you do not understand databases yet and can only do web pages.
We have not even discussed Cobb and Date’s theories of relational databases. I recommend you read and understand their theories before you start yapping about these theories. SQL engines are not true relational databases. What happens if we have to change the locations of data? Stored procedures would only have to be changed where embedded SQL would mean the application would have to be rebuilt and re-distributed.
Best Regard,
Bob Zagars
Senior Software Engineer
Has anyone heard of an ORM? I mean, seriously, this is 2006
ORM is the Vietnam of our industry:
http://pluralsight.com/blogs/dbox/archive/2004/10/04/2621.aspx
example:
http://www.kuro5hin.org/story/2006/3/11/1001/81803
Jeff Atwood on April 18, 2006 1:14 PMI do not know why people say using procs are a headache. If you know what you are doing, it is not a problem. If you work in an environment that processes or manipulates millions of rows, you will know that you need procs in this case.
Also, I cannot imagine putting business rules in a client side app. It may be a good idea to create objects on an application server but if you do not take this approach then you need a database stored proc. Enterpise wide, corporations tend to share processes. If your rules are built into the client then you cant share anything. Reuse across the enterprise is important and not all departments use .NET
You must ofthen think globally across the enterprise.
If your rules are built into the client then you cant share anything. Reuse across the enterprise is important and not all departments use .NET. You must ofthen think globally across the enterprise.
So putting business rules in stored procs, and tying your enterprise to Oracle/Microsoft/Sybase in perpetuity.. that's a good idea?
How about putting those rules into web services or http REST services that work on any platform? Now *that's* thinking globally across the enterprise.
Jeff Atwood on May 1, 2006 3:11 AMWarning: rant on database (in)dependence ahead.
Over the past decade or so I've worked with several RDBMSs: Oracle, Postgres, MS SQL, DB2, Sybase, and a little MySQL (wasn't a fan). If there's one thing I've learned is that each database has a fundamentally different architecture, and database independence is NOT easy to achieve. I'm not even sure if it's desirable.
Switching databases has always been a big, big deal in my experience. This coming from a guy who started on the completely opposite end of the spectrum. I did everything generically - this is just the perspective I was taught in college and literature (black boxing and all that). The first way I ever used a database was by querying MS-SQL doing "select * from mytable" and iterating through the results. I realized that was stupid and learned this whole SQL thing. I learned about ANSI SQL and how I should try to conform to this standard to achieve independence. Then I realized I was using the lowest common denominator and achieving poor results. And coding became much more difficult - had to check against the ANSI standard before using a particular feature: "Darn, x isn't 'ANSI'? Well, I'll do it the hard way..."
If you just replace one database with another, plug-in style, you're going to notice differences (perhaps "bugs"), even with the latest and greatest ORM. With the exception of trivial applications, these differences will affect your application, potentially drastically. For example, you have two users running a transaction on the same row(s) of data. Due to fundamental differences in concurrency mechanisms, Sybase might deadlock (and so one user is rolled back and gets an error) while Oracle runs smoothly. Or MS SQL will give you one answer while Postgres literally gives you a different answer to the same query! And yes, I'm talking about setting the same isolation level in your ORM...
If you use DB2, you're going to have to use repeatable read isolation to get a consistent result set. Not the case with Oracle.
Database A may give you non-blocking reads; Database B does not. Therefore your app plugged into database A may run just dandy. You switch to B and your users complain of slowness. Maybe you then conclude that Database B is bad, but really the problem is that you perhaps unwittingly designed for Database A, thinking that you were database independent all along.
Many developers (not all) want to treat the database as a black box - they feel they don't need to know anything about it - they believe they should AVOID knowing anything about it in order to be good object-oriented programmers. The culture supports this notion with terminology such as "data store" and "persistence layer" - just a place to plop down your data and take it out again later, as if it were spreadsheet. In reality, most RDBMSs are very robust with countless features to manage, manipulate, safeguard, and serve your data to many concurrent users.
Why not use the features that make your database special? If you're using a commerical database, you paid a lot of money for these features. Why use the lowest common denominator? Using Oracle's analytic queries I've seen queries run literally orders of magnitude faster than lowest-common denominator queries/code. I've seen an overnight job transformed into a sub-minute job by using Postgres's native SQL that lets you do, through a single query (not row-by-row): "If a row having the matching ID exists, update, else insert".
I find it much easier to rewrite the database API (stored procedures) entirely than to deal with the above issues. I've used ORM in the form of Hibernate, in order to solve the database independence problem (wasn't my choice), and I found the cure was worse than the disease - I'd rather translate my 100 PL/SQL functions to PL/PgSQL (maybe a bad example as they're nearly identical - or maybe a good example as to why it's not always hard - actually both databases support Java stored procedures so we could even go that route...) than deal with 50 Hibernate XML files. And guess what - even if using Hibernate were easier for me (I realize it's an opinion), it never solved the aforementioned problems.
So I typically push for database dependence, aka "application independence" or "using the database to its fullest potential", not database independence. At one job I was told by my supervisor, "all database access must be done through the business objects in order to achieve database independence." He wasted his time writing his own (buggy) security. Database tables lacked all but the most basic constraints, and contained strings of pipe-delimited name-value pairs rather than using native database types. I asked what we will do if we want to hook up Crystal Reports or MS Access or even (gasp) run an ad hoc query from the command line app? Not allowed, I was told! He wanted to be database independent so much that he sacrificed application independence. He was saving his spreadsheet. Thankfully, most perspectives are not quite as extreme...
Think of how many application technologies/languages/paradigms have come and gone. What do you think is important to companies now: Their 12 year old web apps (remember when "CGI/Perl dynamic web pages" was the hot thing to put on your resume?) vs their RDBMS. How about their 10 year old VB/MS Access/C++ Client/server apps vs. their RDBMS? 20 year old green screen apps vs. their RDBMS? I've developed web apps using JSP/Servlets 3 to 5 years ago - I've been almost laughed at for not taking advantage of EJBs. The author of this Hibernate book claims most EJB projects failed and ORM is the wave of the future. You gotta keep up with the times - i.e., scrap the all-exalted paradigm of 5 years ago for the new godsend. So... your app vs. your database - which are you more likely to redesign/throw out within 10 years and which are you going reuse/depend on?
As always, just my opinion. I apologize if the above post isn't the best organized...
Angva
you can't compare apples to oranages!!!
jeff u need a vacation...and yes start using sp.
you shouldn't be debuging your sps if your design is right. have all your business logic in one tier, data access in another, and data retreval in the sp, then you will have OOP and a very easy to maintain application. and if you like web services, that is just another tier on top of ur business tier, and yes it is platform free if you like that idea.
why would u recompile ur own business tier for a sql change? say you forgot one row, or new rows are added in the tables. come on, i would rather just recreate the package or sp leaving the business components unchanged.
and if ur really gonna distribute ur sql queries to the client side, have fun redistrubuting patchs all day =p
Yang Yu on May 10, 2006 12:28 PMWell I have nothing majorly useful to add but I am a developer, stored procs are a nightmare to maintain and I don't like burying business logic in them.
ORM for the win.
Ian Warwick on May 24, 2006 7:58 AMI am an oracle DBA. from my point of view there were some benefits for stored procs. like bandwidth utilization. but new servers are connected with GB/Sec lan and bandwidth is no more a bottile-neck. Now things are moving to more distributed env. like grid computing. now there is no point in putting bussiness logic and database in one place and say it is better.
In my experience Stored Procs is the major factor in downtime of the database systems and they don't have measurable benefits.
I do not find writing and debugging stored procedures to be particularly difficult. I am surprised that some accomplished programmers apparently do. While I support the objection to an absolutist policy about using stored procedures, I very strongly favor the use of stored procedures over in-line SQL for the reasons already delineated, but especially the consistent client interface they afford. As for maintenance of in-line SQL in client source code, "Edit-Find And Replace-Find In Files" only works if you actually have access and rights to all of the source files of every application that accesses the database. Personally, I find this whining, harangue against stored procedures to be a type of "Coding Horror" in itself, and from the responses, it appears I am in the majority.
Almost everyone here says either, that using stored procedures is BAD, or, that using stored procedures is GOOD.
After reading the postings, I think, some developers code their complete business logic in stored procedures, while others completely refuse the use of SPs.
I miss a discussion about WHAT should be implemented in SPs and what should not be implemented in SPs. I can benefit from nearly all the advantages of SPs without suffering from their drawbacks, if I do THE RIGHT THINGS in stored procedures.
I think, if SPs are used only to hide the persistence layer's implementation details from the DB-client (hopefully this client is the business-tier), then they are exactly used for what they are good for.
If they are used for hiding business logic from the business tier, then they are exactly used for what they are NOT good for (Anybody surprised?).
I think, that EVERY layer should hide his implementation details from every other layer, and I am sure, names of tables and fields, DB-structure and so on are implementation details of the persistence layer. Stored procedures are a way (maybe not the only way) to hide these details. In my opinion 'classical' use of ORM just hides the implementation of the persistence layer in a dedicated part of the business layer. This is no reason, not to use ORM at all, but in many cases it can be a reason to make well considered use of stored procedures.
der yaccob on July 4, 2006 7:04 AMAs a Business Analyst and amateur coder, I learned a lot from this discussion, but was a bit surprised that no one mentioned transactional integrity (commit/rollback etc).
Sometimes we need for several updates EITHER to work in their entirety, OR for everything to be backed out (e.g. moving money from one account to another). At other times we want several changes to be made visible to other users all at the same instant (as in for example changing a booking from one seat to another in an airline seat booking system).
How would you guys evaluate SP vs inline SQL vs ORM etc. etc. in this area?
John on July 21, 2006 10:20 AMAn enjoyable discussion, folks.
IMO SP is great for data tasks required by several application modules, and I'm comfortable writing them. Unusual data tasks get buried in C# T-SQL code - which are limited to queries. I use extensive API modules to interface between SQL data and application layer.
It seems some of us are comfortable in writing and using SP, and others in code. Perhaps the best approach is a blend - let each do what it does best in the hands that weild it.
hi there,
what could be an argumentation against following statement (this our current situation):
* DB enforces useage of stored procs for EVERY DB acces
* main reason = they must have the possibility to change/tweak/optimize all DB related stuff DURING RUNTIME without the need of a recompilation or other application dependent activity
= this leads to a "abuse" of ORM tools where we try to interop with stored procs within ORM tools like hibernate (which in fact is little bit crazy...)
= but is there any argument against this STRONG requirement from a "almighty" DB section ???
greetings from vienna
gerold kathan on August 23, 2006 12:33 PMThe debate have been continued for about 2 years. This is one of the typical software design question that have no absolute yes or no answer. When dealing with this kind of question, developers usually analyze the question in terms of pros and cons, but if we go back and consider the non-function requirement first. It will be easier for us to figure out which solution is better in all situations.
First we consolidate all non-function requirement here, definitely most of them can be achieve with or without using stored procedure, but usually for certain requirement, one solution is better than another. So after we build the full list, we'll have a rule set that can help us to determine we should or should not use stored procedure in our own scenario.
1. Performance
Usually this is the strongest reason for using stored procedure. Without doubt SP can make database operation faster. However it is also a typical remedy to poorly written queries and poorly designed schema. If you believe in the good old 80-20 rule, doing all DB operation in SP is a waste of development effort. With modern profiling tools it is easy to separate slow queries from others. So you just need to tune those queries that really matters. Or wrap them up in SP. On this point, I think a mixed approach is more suitable.
2. Database Portability
Stored procedures are never portable, but such portability is not always required. Many enterprise use one single RDBMS product and never change. Usually, under two scenario this requirement is necessary:
1. You application is a product and your end-users can use it on top of different DBYouYou
2. plan to use a different DB with your application in the future.
3. Security
This is usually necessary if you have more than one applications accessing the same DB, so you may just grant certain application to access SP that it really needs, and hiding the entire schema. However, that means you are using the DB as a point of application integration. It may be the only way before we have other application integration technology like MQ or web service. If you have only one application accessing one DB, and doing the integration outside DB. Why you need SP for security?
4. Service Interface
SP can be treat as interface of service for your application, but now web service is a better way to do so.
5. Unit Testing
No matter you use SP, or data access layer with plain SQL, you have lots of ways to do unit test. So no difference.
6. SP as business logic layer
PL/SQL and T/SQL is not a good language to implement complicated business, better do it in business service layer.
7. SP as a layer to maintain data integrity
SP may be a more nature place for maintaining data integrity. Though there is no big different if you do it in the data access layer.
8. Stop the ripple effect of DB change
Both SP and data access layer can do the same job.
Finally I want to point out another down side for accessing to DB with SP only. Such practice will easily direct developers to produce a too database centric design. And put too much loading to the DB server. As a matter of fact DB usually is a component that is most difficult to scale up. In contrast, the mid-tier can be scale up easily with modern load balance technology. No matter we use SP or not, we can do some trick like cache to reduce the number of DB access. That can also enhance the performance.
Boiling Java on September 15, 2006 3:13 AMStored procedures are a travesty to software development everywhere.
All arguments for it ignore the fact that any application with a well thought out architecture will have a data-access layer that will make direct SQL calls from the rest of the application unnescessary.
Why write any code that manipulated data in archaic pseudo-languages of sp? Just write a few libraries of code that will act to abstact the data retrieval and update from the database.
There is really no excuse to burn through development time and resources writing and debugging stored procedures. There is no clear advantage to doing this and in my mind represents a huge flaw in architecture.
Bob on October 9, 2006 6:32 AMObject-relational integration people. You don't put business logic rules and validations in the database. Oracle would love to compress the whole N-tier world down into the database engine but it's not needed. Stored procs are useful IF you need to grant individual users security access to specific limits things. However, in practice, most large scale business systems use a generic unrestricted ID to access the database from the app layer and apply access rules there anyway. If the data universe is encapsulated in an object-based access model that hides the underlying relational db structure and uses parameterized SQL to interact with the db engine, that's all you need. Stored procedures then become an unnecessary maintenance annoyance with little practical performance benefit.
Archangel on October 11, 2006 3:31 AMThe majority of this is simply retoric from MS from a time before there were proper ways of handeling re-use at the database level, that is repeated blindly by the MS SQL masses.
Having a long standing background in Oracle as well as MS SQL, and being extensible capable in both PLSQL as well as TSQL, I can safely say that I have yet to see a single argument in over probably 6 years now, that could convince me there is a need force everything through SPs.
Performance is a load of garbage. Before you people keep repeating this, test it out yourself sometime. Write a program that executes a parameterized stored procedure, and a parameterized sql, and time it as you execute it over a loop 1000 times. Let me know which one wins (hint, it might not be the one you expect, especialy if your querey has any complexity at all).
The fact of the matter is keeping your DAL in your stored procedures is now the lazy part. Get off your butts and write a meaningful API that can have multiple concurent versions, support modern language constructs, participate in actual OO design (as you can write your API in an actual language), access multiple data sources, support distributed and grid models, follow accepted design patterns (abstract factory anyone?) and lead towards a true SOA.
Just because someone told you 5 or 10 years ago that SPs are good doesn't mean they are. We've moved on from that ladies and gentlemen.
Oh, and please people, seperating your database code from your application doesn't make something "OO". (Read the above posts, it happens too often). OO is a form of seperation of concerns, it is NOT seperation of presentation layer :P geez.
heh, I must add, ORM isn't the answer either. Please please be careful with this technology with any sort of mid size or larger project. It looks so promosing to start, and before you know it you're giving up flexibility, functionality, performance and maintainability, and you're not really getting any benefit out of it.
I know it looks so promising up front... but please, just be careful ;)
Dust on October 18, 2006 6:30 AMhow to write stored procedure in mysql
satish on November 6, 2006 11:34 AMStored Procedures are written in big iron database "languages" like PL/SQL (Oracle) or T-SQL (Microsoft). These so-called languages are archaic, and full of the crazy, incoherent design choices that always result from the torturous evolution of ten years of backwards compatibility. You really don't want to be writing a lot of code in this stuff. For context, JavaScript is a giant step up from PL/SQL or T-SQL.
Stored Procedures typically cannot be debugged in the same IDE you write your UI. Every time I isolate an exception in the procs, I have to stop what I am doing, bust out my copy of Toad, and load up the database packages to see what's going wrong. Frequently transitioning between two totally different IDEs, with completely different interfaces and languages, is not exactly productive.
Stored Procedures don't provide much feedback when things go wrong. Unless the proc is coded interally with weird T-SQL or PL/SQL exception handling, we get cryptic 'errors' returned based on the particular line inside the proc that failed, such as Table has no rows. Uh, ok?
Stored Procedures can't pass objects. So, if you're not careful, you can end up with a zillion parameters. If you have to populate a table row with 20+ fields using a proc, say hello to 20+ parameters. Worst of all, if I pass a bad parameter-- either too many, not enough, or bad datatypes-- I get a generic "bad call" error. Oracle can't tell me which parameters are in error! So I have to pore over 20 parameters, by hand, to figure out which one is the culprit.
Stored Procedures hide business logic. I have no idea what a proc is doing, or what kind of cursor (DataSet) or values it will return to me. I can't view the source code to the proc (at least, without resorting to #2 if I have appropriate access) to verify that it is actually doing what I think it is-- or what the designer intended it to do. Inline SQL may not be pretty, but at least I can see it in context, alongside the other business logic.
When an application is made by a software house (take note!) adhering to CMMI L5 SP choices (why are other not focusing on User-Defined Functions? it is really most taxing to study another language again in the RDBMS) are debated just on the Business Analysis Phase from among other choices. Designs, backward compatibility, languages of choice, technology, RDBMS among others. So any blames will be passed on the Business Analysts and not on the developers.
(document everything)
When an error is incurred within the embedded code of vb or java or c and SQL, the error can not be located since a programming language are not made to check line per line in the SQL statement embedded with it. The enterprise environment of SQLServer can do the same and save the SP/UDF without syntax error. SP/UDF requires specific error handling that may be even longer than the SQL code itself. (document everything)
Codes are not supposed to be rushed, they should be thoroughly tested and there should be separate team just to test the codes. Specific error testing codes should be inserted in between evaluation to prevent unlikely errors. SP/UDF are another language distinct and separate from vb or java or or .Net. SP/UDF receives parameters and passes return values. (document everything)
For me SP/UDF/UDT/Trigger is the only way to go since programming language takes long time to tame and if another problem with SQL adds to it, it will double or even triple the team's development time by more than twice.
elmercarandang on December 14, 2006 1:34 AMStored Procs -versus- embedded SQL
1. You have a critical, large application that must run 24/7. Whenever a critical production error occurs you have to fix it, redeploy to a test environment, test, and redeploy to production. If the issue/error happened in a stored proc .. you have cut your final deployment time by about 100%. In The Real World "Time Is Money".
2. Changing C/Java/src-Code is inherently more risky than making a change to a stored procedure.
3. The simple matter of testing a stored proc change in a UAT environment is so much simpler, safer and easier than testing a application-code change ... silly to think otherwise.
4. Bandwidth becomes a BIG issue if you have an application with a heavy thru-put and large user community. For example, the need to upload huge amounts of data and process that data should always be done using some native load utility in conjunction with a stored procedure to process the data.
5. Security:
Map all users to a functional id. Map the functional id to an entitlement mechanism maintained in the database and accessible only via store procedures. Associate the functional id to a group that grants execute permission on the stored proc.
6. Guess everyone knows about execution plans, update statistics etc.
7. Distribution of processing and io ...
-----
Thru experience and observation ... will come clarity of common sense.
Response to George..
1) The only time you would save is with the deployment, unless you don't test your stored procedures. However the main time I have seen where this happens is when people used stored procedures for everything; in cases where people put thier CRUD in the code is is alot easier to test and thoses type of situations do not happen. If you cannot catch that a select col1,col2 from tab1 vs the more complex stored procedure during development then your testing is bad.
2) it is only easier if you don't do source control and don't do testing. Otherwise it is far easier to change the code and submit through standard testing.
3) It is only easier because you are not using source control and testing outside of production environment. Nor are you testing that the stored procedure is used outside place that alerted you to the problem. If the stored procedure is being used in mutliple location you better test all thoses locations. CRUD dynamic programming does not have this problem, far easier to test all location since you reused the code it is for the exact reason it was originally created, where with stored procedures you tend to use a stored procedure that gives you what you want even if it gives you more then what you need.
4) While you will save a few 100 characters between sending the query and the name of stored procedure you will quickly lose it because people don't create stored procedures for every case so you will get sent back alot of data that is ignored. For example say you have a table of 15 fields, with dynamic CRUD programming you will just select the fields you need with stored procedures you will generally have 1 stored procedure per table that returns all fields do one request with that stored procedure when you don't need all 15 fields and any bandwidth is gone with just a row or two. Or you could create custom stored procedures for every select you need; have fun with that.
5) Not many people still do individual login account. Most permissions is done against a single account using roles to prevent access to places people don't need to be. Users don't have direct access to the databases and for the few commerical tools that do need this requirement usally work best with access to the tables and have problems using stored procedures for everything.
6) As a DBA I could care less about looking at stored procedures for determining indexes, etc I use the logs to see what is actually being used and base tuning on that. Also since MS-SQL Server 7.0 dynamic SQL and stored procedures are not processed for excution plans any differently. What stored procedure do give you is the queries will be similar enough that the same execution plan could be used; I use a development framework that help ensure that on the code side.
7) CRUD stored procedures are bad for processing. ISNULL and COALESCE take huge amount of CPU vs a straight CRUD command and and also very slow to execute.
will dieterich on January 31, 2007 4:36 AMI agree totally about the pain of coding in database languages. I am an experienced programmer but relatively new to databases and started experimenting with stored procedures recently. I wrote a routine in T-SQL to determine if a string was a valid IP address or not, and it reminded me of writing BASIC as child.
Maybe I'm just spoiled by scripting languages (Python, Perl, PHP) with regular expression support. It must have taken 20 lines of code to iterate through the string character by character, etc. Something that would take one line of code with a regex.
Bill on February 3, 2007 12:44 PMIf you are doing alot of stuff with regex in MS-SQL server there is a decent regex package for version 2000 on code project http://www.codeproject.com/managedcpp/xpregex.asp
If doing it in version 2005 then it is really simple as shown in this example http://blogs.msdn.com/sqlclr/archive/2005/06/29/regex.aspx also the demos that come with MS-SQL server or one of the add on development samples has all the code for doing it.
will dieterich on February 5, 2007 1:20 AMAll the parameter depends on the way the stored procedure is defined and the expertise of the developer in the stored procedure. If you dont use a proper exception handling mechanism, you will be in a mess. Data validation on table data would always be done in stored procedure. It only have a more control over the data in the tables than Java or .net code.
Prem on February 9, 2007 3:39 AMYou are SO RIGHT, Jeff Atwood!
I've been reviewing a stored-procedure-laden application for my company... You would not believe how much time is wasted writing, debugging, installing, compiling, and trying to figure out what else is wrong with a (hand-written, several megabyte) package of Oracle procedures.
Let's take the cost of our time writing and debugging these stored procedures and call it X. Lets then take the savings of not writing and debugging these stored procedures (with a substitution of NHibernate or another SQL-less ORM tool) and call it Y. Let's take the cost of processing power (e.g. a web farm) that may be required because the app isn't utilizing the efficiency of stored procedures and call that Z. If Z - Y X Then it's safe to say that you should never use stored procedures. Z - Y X has been true in all scenarios that I've encountered.
anonymoose on February 16, 2007 1:23 AMRE Who needs Stored Procedures, anyways?
your attempts to pontificate intelligently do nothing to mask your ignorance.
please head on ; I can make a good living cleaning up the messes you genius's make.
"can not think of one VALID reason for using messy inline code, Not one, using SQL in the db gives you Scalability for one, Security and a lot easier to use."
There are plenty of good reason not to use stored procedures for your CRUD, just read the longer posts in this topic. However lets look at your reasons.
Scalability:
Yea you can build out your databases servers to increase scalability it is expensive both in hardware and the licenses, far more then to do a web server or client machines. Also once you get into very high end database they recommend you remove the following items for scalability: Stored Procedures, triggers then joins.
Here is a article about the ebay setup from SD Forum 2006 where they say to scale databases remove the stored procedures.
http://www.addsimplicity.com.nyud.net:8080/downloads/eBaySDForum2006-11-29.pdf
Security:
You can make the case for this for client/server program, but there are ways to decrease that. It is even getting less of security risk because companies are requiring secure communications for all connections.
For web and similar applications it offers no security because the user never has any direct access to the database and has no way of access the database.
Ease of use: How?? The tools suck for editing and testing stored procedures even most of those expensive 3rd party ones, maybe visual studio team for dbas is better, have not had a chance to use it yet. They lack the ability to link in with source control, easily switch version, modern debugging capabilities along with the previously mentioned editing and testing tools. What you usually end up with editing outside of the database, submitting the text file, and then running a test script.
From the management standpoint it is not easy to use, you don't know when they are no longer needed, and if you need make an easy change, say the sort order, you need to create a new stored procedures or add a new level of complication to the stored procedure so that it will return different results depending on how it is called; just don't forget to test all the ways it was previously called.
I'm completely on SP's are bad. Here are some of my personal observations.
In virtually every organisation I've walked into where SPs are policy because of 'security' I've seen DBAs, developers and MI experts have complete access to the underlying data so they can run reports, monitor applications etc. etc.
Virtually every DBA I talk to goes on about business logic in stored procedures; HELLO breaking every rule of good programming: your a database not an application the only concern of a database should be storing data the business logic should be in the app.
Stored Procedures break single resposibility (A change in code means a change in 40 sps means a change in the tables blah blah bugs bugs bugs).
Scalability and speed: I have seen some well complex cycles between apps and stored procedures, creating round trips, using temp tables doing some truly horrible stuff that wouldn't be nessasary if you just did DynamicSQL. An example: how about selecting data based on a set of results a get me all records in this list I've just selected. What SP's don't support arrays? What I have to create a comma delimited list and then send it to do the sp which splits is back up into a temp table and uses a cursor blah blah bugs bugs.
Also SPs are Vendor lock-in. Just migrating from SQL 7 to 2005 has created a nightmare for our DBA team and imagine if we went to Oracle: the differences between the features of each RDBMS are HUGE meaning complete rewrites for each database. Is it me or is that just backwards?
Shorter queries: Change the customer name in an object dynamic SQL sends UPDATE customer (customerName = x) rather than having to send every single value of the customer across to the database. I cut my queries down several hundred bytes thanks to dynamic sql.
Security: anyone heard of application roles? You don't have to grant every user access to the tables just the application. Only the application can access the database - surely thats better security not worse.
Every application with SPs I've ever worked on has had 90% of it's bugs, security and upgrade issues in the SPs.
SPs can be good though. Sometimes it's the best way but in 90% of cases databases are a way of saving data for an application to use so why make life so much harder by slowing down development and introducing extra complication by denying the application to choose the best way to access the database.
Another thing.
First the distinction between hard coded inline SQL and dynamic SQL. DynamicSQL requires very little hard coded SQL and with a good O/R tool it is optimized. I can do this: Customer.Get.Where(Age).Is(GreaterThan(18)).Is(LessThan(5))
Rather than:
command.create("sp_GetCustomers")
command.paramaters["minAge"].Type = DbType.Int32
command.paramaters["minAge"].Direction = Output
.... blah blah
resultsSet = command.Execute()
.... loads of code to turn results set into a Customer object.
What looks clearer/is easier to read/change/maintain?
Also how many times have DBAs changed SPs to introduce bugs. And when was the last time that this happened and the DBA didn't use source control. Hell, does a DBA even KNOW what source control is? So even if my SQL is hard coded at least its saved somewhere I can do a diff on it and get it back.
Jupiter Moonbeam on March 2, 2007 4:50 AMHello, this discussion is very old - but not yet outdated!
I noticed that no-one answered the question about transactions?
You haven't talked about Linq/ASP.NET vNext much on your blog - I'd be interested by a debate on the subject. From what I understand it could be the 'next big thing' - I certainly hope so.
I firmly believe that code that can be generated shouldn't need to be generated - it should be part of the language/framework. So as the previous poster says - I'm very much against writing embedded SQL, but generated SQL doesn't bother me at all.
I might add that I suspect that a lot of the 'anti' arguments are just a cover for people who dont' like/understand sql. And that the 'for' arguments are DBA's who don't want to share their power!
Ben on March 2, 2007 6:09 AMAs someone who wears both programmer and DBA hats, I'm all for stored procedures, for all the reasons already listed above.
I'm from a programming background so I certainly do use source control on all changes to the database. I suspect the percentage of DBAs who don't do version control is smaller than the percentage of programmers that do row-by-row logic rather than set based when writing their SQL code. It's a mentality that can be hard to shake.
As far as transactions go, transactions are transactions. They should be handled either by the dynamic code or stored procedures, but I think that a DBA is going to realize the need more than strict programmers who often aren't as concerned about using all the data-integrity techniques that are already built into a database. I've seen far too many folks reinvent the wheel over and over. Business logic and general data integrity belong in the database.
James on March 14, 2007 7:27 AMdietrich ( excuse spelling of name ).
I think that embedded SQL in conjuction with some type
of ORM tool such as Hibernate has its place in any environment.
But that place is very limited in functionality.
I know that we use both in my enviroment and I can tell you
immediately that the SP calls are much more efficient than the
Hibernate usage in a wide variety ways.
1. Less data going across the network.
2. Extreme localization of data base DATA
3. Much Easier to migrate to and MIX various database management systems.
4. Extreme granular entitlement is possible .. ONLY .. thru the use
of stored procedures. Well .. that is a big statement. Of course you can create some complex form of entitlement using a combination of LDAT and Code .. but I find that this is much more complex that using stored procedures to handle data entitlements, taking the burdon away from your Code.
------
I am of the MindSet that the Java/C++ code really doesn't need to know much about the data repository. This could have to do with the business you work in, also.
------
In the case of heavy transaction processes, the Hibernate/ORM tools
are not functional, i.e. much too slow.
----
Of course I am speaking about a professional environment in which
a simple sneeze could cause a major ripple in the world's economy. Thus, cvs, sccs are up the gazoo, not that I really need to say that, along with access to anything with a P in the name could result in not
only an individual's termination but the complete shutdown of a functional group.
----
I see many deadlocks occuring within the groups that do a lot of Hibernate/ORM processing. The code for these hibernate/orm groups contains embedded sql and also they try use Hibernate's own brand of transaction management. I am not sure if that is what's causing the influx of deadlocks.
-----
Also , I am starring to see a lot of "outofmemory" issues with the groups that are making heavy usage of Hibernate/ORMs. Again, I haven't carefully inspected the code base to see just exactly why these types of issues are starting to pop up. I only know that groups using Hibernate/ORM or having more of these issues than other groups.
-----
I am starting to slowly use Hibernate/ORM and maybe I will change my
mind in time about the use of embedded SQL versus stored procedures. But still, I feel that stored procedures are the way to go when it comes to about 99% of database access.
-----
If you look at it this way:
Simple Stored Procedure approah:
Java/C++ --- Jdbc/rougewave --- Dataserver(sp,most of the data procesed here) --- Jdbc/rougewave --- Java/C++
versus
Hibernate/ORM with embedded SQL:
Java/C++ --- embedded/SQL -- javabean -- Hibernate --- Jdbc/rougewave --- Dataserver(no data processed, all sent back) ---
Jdbc/rougewave --- Hibernate --- javabean --- Java/C++
Now I will say that the underlying code that processes the embedded Sql using Hibernate is very eloquent, having all your returned data nicely mapped and package into a POJO for you. BUT IT IS NOT AS EFFICIENT as the simple stored procedure approach.
But maybe .. after I use a ORM tool ( hibernate ) for a while, I may change my mind.
ps ... anyone here should feel free to visit my webpage ..
I have coded ORMS going back 20 years and nothing has much changed; funny that. The last 6 years I have done more stored procedure work for performance and code reduction on billion+ record scientific databases. Our databases have dozen of applications old and new written to them in different languages and platforms. Business rules in the application would be a nightmare! Since our databases don't have to be portable we take full advantage of a database vendors extensions. I'll give one example: 6 years ago I wrote 3000+ plus lines of C++ code to provide automatic quality control of atmospheric data. Last year I wrote the same application in PL/SQL (95% SQL) in less than 500 lines and it runs 10x faster. For bonehead simple CRUD you can go either way but with real heavy data processing work and statistics etc., I have always been surprised how a database centric design will save time, money, and tons of code. We have SQL here that I cannot even imagine coding in Java or C++ since a relational set orientation to data is just a plain better way to think about the problem then OO; OO is not a decent data model and I have done lots of both.
Eric on April 2, 2007 7:21 AMJust a quick comment. I know the comments are almost inexcusably long already..
Someone towards the beginning mentioned that using stored procedures adds another layer. In my opinion, this is a GOOD thing. It serves as a buffer between the data and the data access tier of your code. If you know anything about OO programming, you understand the need to encapsulate. You don't get this with dynamic or inline SQL.
Not to mention that if you have a schema change or database refactoring, you have to scour your code to see where it is broken. If you use stored procedures, you need only change the procedures to correct the output to what is expected instead of searching through all of your code. If you know anything about OO programming, you understand the need for interfaces and how much of a pain it is when you break an interface. With dynamic or inline SQL, the nightmare is doubly painful. This comes from experience.
That being said, dynamic or inline SQL is not a BAD thing. I am not one of the Stored Procedure Nazis. Sometimes you just don't know what the query will be until you're in the middle of code execution. However, this is more the exception than the rule, and it should be treated as a special case.
Nathan on April 11, 2007 9:52 AMMy two cents. First of all, I notice that everyone is a bit self-conscious about how long their comments are on this topic. From all that I have read you are all doing very well expressing your views. So vent and feel good about it!
I myself am a Dynamic SQL believer simply for the fact that to get optimal query speed whether it be SP or Dynamic SQL, YOU LIMIT THE NUMBER OF FIELDS NEEDED in your request. If you are using dynamic SQL, you simply Add/Remove fields in the selection. Painless!
When you are a programmer trying to increase the application speed you usually find the problem is a process that only requires 3 fields, but for some reason, the SQL was written as “SELECT *…” (because at the time you thought all of them would be needed). If you are using dynamic SQL in a function called “GetThisData” then all you need to do is change the “SELECT *” to “Select Item1, Item2, Item3”and life goes on. For the die-hard SP followers reading, please tell me the easy way to find and then fix this when using a SP for a program that is not just in use for your company, but sold around the world and every customer has their very own database.
Finally, there is a lot of talk about abstraction in support of SP. What is my business layer for? Just to make another call someplace else to get the data? How many more layers do you really need? And are you honestly getting more done with 2 layers that do the same as one?
someone commented stored procedures give you scability. huh? in a web farm, each server can be doing some of the logic that most developers put in a stored procedure. much easier to scale a web farm than a database server.
everyone has made good points but most of us are smart enough to know there is always a trade-off. i use a combination of stored procedures an parameterized SQL. i use iBatis which is a data mapper more than a full blown ORM. iBatis gracefully handles both inline SQL and stored procedures. i use simple stored procedures to enforce relational integrity, for example cascading deletes. in this scenario ORMs are too network chatty. if there is business logic then i try to do it in code using iBatis' DAO classes or parameterized SQL. two benefits off the top of my head:
1) scalability - the database isn't doing business logic processing for an entire web farm
2) business logic isn't interspersed between our C# business layer and in stored procedures
i use a database for what it is, a data store. not only does it perform and scale better when you use it as such, the code is more maintainable.
mario on July 19, 2007 11:13 AMHere are some reasons why I prefer inline (though we do use stored procedures here and there, especially for reporting). t
1. Dynamic SQL. Most of the list screens we have in our application include various combinations of search criteria that may or may not be used. Stored Procedures (at least pre-SQL 2005, which I havent used yet) dont let you conditionally change the statement, you either repeat the modified statement for each different criteria) or you do (field = THIS PARAMETER or THIS PARAMETER is null) style coding which I have seen greatly slow down the efficiency of a query when there are many of those conditions. There are places in our Dynamic SQL screens where we would have to write 1000s of Stored Procedure statements to mimic all the ones that can be generated dyamically.
2. Searching your code. When I need to see where fields are being used for update and insert (which we do not do via stored procedures) I can just search the code directories and easily find the places using it). If in stored procedures I have to do one pass to find which stored procedures are involved and then many more passes to find where all these procedures are called. I believe it is far more maintanable to see usage of your SQL right in your code... whether it is in a DB layer or not.
3. Showing your SQL statements. In our ASP (classic and .Net) we call GetDisconnectedRecordset(), ExecuteSQL() or GetDataset() functions (or methods) that wrap around the native ADO code. In doing so, we can consistently trap errors, record out of bounds timings (outside of performance monitoring), and even more importantly FLIP A SWITCH ON OUR WEBSITE and instantly see every query going on to load every page and during every update. That alone is worth its weight in gold. Debuging in many cases is reduced to seconds. And for new people.... you want to see what tables and fields are being used? Turn on ShowSQL and get your answer right on screen!... HUGE!... and possible because its INLINE SQL and all run through common routines. Our common routines also include logic to look for, not process, and alert us to any injection attacks. The "showSQL" functionality when in classic ASP shows up right on the page where the SQL is called (very 2 tier)... in .Net where we have a data layer, our SQL calls write their processing info to a string that we can then get the value of pre-render to display when ShowSQL is on (which is only allowed for developers)... so that is a way to still keep your tier separation, if your app needs it, but still have showSQL functionality. The other thing I can note, with our inline usage, whenever we choose to move a statement to a stored procedure, we turn on Show SQL and in cutting and pasting we are at least 80% of the way there in one shot. We format our SQL statements in code with spaces and carriage returns and on output replace with %20 and BR so the statements are VERY readable.
4. Many people dont like to talk about the actual needs of the application you work on. Do you need true Ntier separation? Are you overworking for potential scalability that may never matter? If you structure your app well, it should come down to matters of organization where you could separate to different DLLs and physical machines IF you need to. If you are building EBAY with millions of hits every hour, you have different needs than a company specific app not exposed to the public... with 1000 users and only 100 active at any time. In that type environment, the complexity of splitting your tiers up so dramatically may not yield you much benefit either in development or performance. In fact, the more tiers you have, the more scalable you MIGHT be (if you did it well engouh), but every tier adds more going on in terms of processing. If you read your data from the dataset/recordset and then move it to XML, array, string, etc, and then have to unpack it in the next tier just because you refuse to use DB objects in that tier, imo, that can be going too far in many situations and slowing down your processing needlessly. Calling your SQL within your ASP page code isnt always automatically wrong or due to having weak experience... you can make a case for your situation that it might be the right thing to do (especially if you arent dealing with lots of it and lots of concurrent users). Part of our job is to use the right solutions for the task at hand and there is such a thing as overworking a solution! Same with objects... object oriented isn't automatically better, especially on the web. If you are building robust objects and now your page needs a few things from a few "objects" but now you have to load all of them completely to get your data, that is overkill compared to having SQL return only what you need for the page in a statement (even though that is not an object-oriented approach). I always believe the NEED at hand is the best way to dictate the approach... not a blanket approach for everything. I have done many n-tier systems and many two tier... neither is truly an EVERYDAY right or wrong approach, each can be the right approach depending on the need. Especially on the web, if an area lends it self to business objects... do that... if it doesnt, dont be afraid to NOT use business objects there. Ultimately, efficiency matters... and in many cases inline SQL right in the page is going to be more efficient and responsive. As someone said, inline vs stored proc is always an evaluation of pros and cons... both from developer viewpoints, and from the needs of the application. What is right in one case might be wrong in many others, but it doesnt mean each approach itself is always right or wrong.
5. Maintenance. If every SQL call in a large app is in stored procedures, I think you have a huge mess. It's alot to look through... and SQL Server (or most database tools) do not give you much in terms of choices in organizing. SQL 2000 and before (again, I havent used 2005)... all stored procedures are in ONE directory and you only control the naming. Even though you can use .SQL script files to initiate changes (and get into source safe) (which we do) it still becomes a mess juggling all of these... especially when some are so simple they hardly are worth the bloating of the DB objects.
6. Bugs due to stored procedures separation of statements. If you add new fields to a table and thus have to change insert and update stored procedure to accept these fields, but have 6 apps using it. As soon as you change the procedure, you better have updates for ALL those apps in place or else they will all break on the change. If you add new fields and use inline code, each app will generally work fine with the new fields there (if they allow nulls) and you can then work in changes to use those fields by updating those apps one by one all the while the prior version still runs (and there are cases where this is useful). Again, its relative to what your system does, who the users are, and how its rolled out, among many other things.
6. We use Crystal Reports... and the last thing we want is Crystal controlling the queries... so there we use stored procedures that Crystal Reports will work with.
7. We have a couple cases where we couldnt run a statement inline (too complex: many joined tables, group by clauses in statements acting as tables within outer queries that are also grouped) and moved it to a stored procedure because that made it run. Some would say, you shouldnt write such complex queries. I say, in relational databases, where you have many tables involved in the answer (this is what normalization leads you to often) if you can write a statement that is VERY complex but runs in 2 seconds compared to one that is made up of many simpler statements but then takes 40 seconds... I lean on the speedy approach!
For me, comparing inline SQL to Stored Procedures is like Comparing Manual Transmissions to Automatic. I think Automatic can isolate you from many things, but in the end, there so many more pros to the Manual transmission that Automatic cant hang with... and thus, thats also why I choose inline over Stored Procedures. It's not a product of being "lazy" at all, it is that there are many overwhelming advantages that taken altogether make it, for me, the best choice... IF YOU USE IT SAFELY AND WISELY and focus on efficient statements and injection protection. I also hate the notion that you have to isolate the DB from your junior or UI only developers. If that is how you run your business, imo, you run your business wrong. If developers cant handle DB work, they simply shouldnt be developers! Developers need to be encouraged, trained, and expected to handle DB coding and learn how to write efficient SQL Statements (whether inline or in stored procedures), they either have to learn or should leave the profession. Having DBA handle all that and isolating the database from the developer is not an approach I favor. Having DBs review things, look for problems, and help developers, on the other hand is just as valuable as having people who do nothing but test the application!
When applications get too rigid in rules and layers and layer separation, I find very often functionality is skipped because its too hard to do given the structure of the app and the layers. That is a shame, because ultimately, a system is used at least 90% of the time to make someone's job faster and easier... so the tools and techniques should not routinely impose limits on that goal!
Finally, inline SQL does not equate to crappy sql. You get way more bang for the buck in learning the right techniques to speed up your SQL Statements (hit indices, use joined table results as its own table as if the results were a single table and then join to those results)... and those techniques can produce amazing results whether inline or stored proc and they are the more important bang for the buck. Crappy is independent of tools and techniques. You can write ASP with inline SQL that is very clean. You can write .Net with stored procedures where its a mess. The nature of there being a mess is not driven by or limited by the tools used!
(Sorry for the length, but I unless I missed something, I think I added some significant additional thoughts here).
Too often, I think people use a certain tool or technique because they were told that was the way to do it... and they believe it like a religion. Think deeper than that... there is place for just about everything.... stored procedures arent always wrong... inline SQL is certainly not always wrong... but how you use that mix really depends on your application, its purpose, and the size and needs of the user base. I have yet to work on any application where stored procedures for all queries and insert or update statements would have been the best approach and in every case I would have greatly missed the benefits that inline SQL provided in comparison!!
Jeff W on August 15, 2007 1:02 PM"Anyways" is not a word. The correct usage is "anyway." You wouldn't write "anyhows" would you?
Pam Tree Pete on September 12, 2007 11:35 AMLets put things in perspective. I work at a place where users build asp.net applications. They stick SQL wherever its convient and they spit out a working application in 2 weeks. Does inserts, updates and all the things that a lot of you would pat yourselves on the back for.
Here's news for you. Cracking out apps without any regard for good application design is is not rocket science. You don't even have to be that intelligent to do it. I could teach just about anyone basic programming concepts like conditional statements, for loops and basic SELECT and Insert statements in a couple of weeks they'll be writing applications that are just about as good as a lot of "professionals" out there.
Without going off on a tangent, calling stored procedures from your code behind or windows form is not a good idea either. There's something called a "business layer" which is used to abstract your "data layer". Look it up.
Oh and how many of you actually have had to access databases from different vendors anyway?
So instead of defending putting your SQL Queries Inline, why don't you crack open a book on application design and learn something that might separate you from some guy in India with a "teach yourself SQL in 5 minutes" book making 4 bucks an hour.
Mike C on September 20, 2007 7:59 AMYou wouldn't write "anyhows" would you?
Maybes I would.
Jeff Atwood on September 20, 2007 11:44 AMWhat a load of bollocks. I completely agree with Mike C on this. SPs make code more maintainable, increase performance and help when implementing N tier applications. It also allows you to share business logic between the database and the business objects. You probably write loads of nasty code with UI logic mixed up with business logic. And the argument about SP compatibility, initially this can be overcome by creating new SPs with structured names to indicate version or by simply setting arguments to default values and ordering them correctly.
Try Rocky Lhotkas CSLA.NET site for some rock solid techniques on how to implement data access and business logic using his framwwork.
While you guys are flaming use of English (not American) what does the phrase "Go Figure" actually mean. Its not a sentence is it. Surely its "Go and figure it out yourself" is the correct use!
looking through most of these comments, it's almost as if people don't know you can use default values for stored procedure parameters that aren't specified at run-time.
Walter Matthau on October 18, 2007 6:51 AMMy two cents:
I’ve heard this about stored procedures:
1. They are more secure. But... in every database I’ve seen, the database user has dbo access. BTW, I work in a large bank. Go figure.
2. You can debug them as easily as .NET code. But... I’ve never seen anyone set up SQL debugging in Visual Studio. Most developers run the SP and hope it works.
3. They can be version-controlled. But... try putting them in SourceSafe and see if you like it. Honestly, can you tell me how this SP looked a year ago? What were the last 5 changes to it? Who made them?
4. It’s easy to control schema changes. But... if you’re planning to rename a table, is it easier to hit Ctrl-F in your code and search (or search VSS), or to assess this impact on SPs in the database? Sysdepends is great, I know. I guess I could just script the entire database, dump it to Notepad and look for the table name. Sure.
5. They’re faster. But... nobody seems to be willing to prove it. And even if they are, nobody cares that an SP is 10ms faster if it’s 10 times harder to maintain.
LINQ, anyone?
Eerie Inhabitant on October 19, 2007 2:16 AMThis debate has been going on for how long, almost 4 years. As I said in the past I have been developing database application since the late 80’s and electro-mechanical devices since the mid-70’s.
We forget that we deal with 1’s and 0’s which running in resident memory is always the fastest method. Anytime we transfer that information via storage or network connections it slows down the process. So I am not advocating only stored procedures, you have user defined functions, views, triggers, and other parts of a DBMS to compile the data to be send back to the requester.
Now companies in the 90’s to make budget developed the easiest methods and many times they scrimped on quality (who won Beta or VHS?). Does not mean it is the right way to develop applications. Also because of market shares and business the best methods are lost to marketing and who has the money.
Still I content that front-ends and data manipulation must be separated to be a successful system. In long term managing of data it is the safest method. Microsoft Visual Studio allows several technologies to protect your business presentation layer from the user interface. Remoting or web services are two technologies to keep your business presentation layer separated from the UI, and ADO.NET controls data concurrency with industry databases.
Now I do think Microsoft or one of its development partners should develop a debugger for SQL that works within Visual Studio. I know the technology exists and have used it successfully in the past. It does need two parts one that resides on the database server and one on the client development computer.
Anyway stored procedures have a valid part within our IT environment. As developers we have to decide on response times and manageability of systems, also availability for the specified business. Some systems were built on limited technologies, it does not make them wrong but they could be improved upon.
Bob Zagars on October 29, 2007 9:21 AMMike C: "I could teach just about anyone basic programming concepts like conditional statements, for loops and basic SELECT and Insert statements in a couple of weeks they'll be writing applications that are just about as good as a lot of "professionals" out there."
Do you seriously believe that? That is the stupidest thing I've heard in a while!!
Petey on October 31, 2007 9:41 AM"I'll give one example: 6 years ago I wrote 3000+ plus lines of C++ code to provide automatic quality control of atmospheric data. Last year I wrote the same application in PL/SQL (95% SQL) in less than 500 lines and it runs 10x faster."
Dude! What the hell are you doing in your C++?
If automatic quality control means "sorting and searching" for stuff, then sure, SQL would achieve it nicely. But anything other than sorting and searching needs to be done in business logic, i.e. C++ code.
Petey
Petey on October 31, 2007 9:50 AMWow, I just got done reading every comment on this entire page, and I still don't know what to do. I've been given the task of refactoring (more like overhauling) a system which makes judicious use of inline sql in the very BAD way ... no prepared statements, no data access layer ... just concatenated strings passed to JDBC. It makes me want to become a dentist.
But any-hoo ... I wanted to completely redo the way this application accesses it's data, which it must do in several different business environments supporting both MySQL and Oracle. Do I create stored procedures to handle similar data needs in both databases? Do I simply use Java's PreparedStatement object and centralize things in a nicely compact DAL? Do I create a Servlet to run on the DB server awaiting my demands? I've learned quite a bit by reading all this stuff ... except what is the right thing to do. Help me Obi-Wan Kenobi.
Mike on November 2, 2007 3:20 AMTweedledum: I love TSQL and writing stored procedures. ALWAYS use stored procedures. If you disagree with me you're an idiot.
Tweedledee: I love C# and writing my select statements using a SQLCommand object. ALWAYS stay away from stored procedures. If you disagree with me you're an idiot.
Pathetic!
You people make me embarassed to be called a software developer.
"Tweedledum and Tweedledee
Agreed to have a battle;
For Tweedledum said Tweedledee
Had spoiled his nice new rattle.
Just then flew down a monstrous crow,
As black as a tar-barrel;
Which frightened both the heroes so,
They quite forgot their quarrel."
Where's a monstrous black crow when you need one?
Lewis on November 19, 2007 10:03 AMIt is quite laughable to hear arguments against the stored procedures. As many people have already said, this is not about the amount of code (with quotation marks, concatenation, conversions etc SP win that argument hands down). Most importantly, stored procedures provide a modular design. A good coder does not ponder of SP only after completing the calling constructs. That is for juniors. The SP is an interface to a data server. So, the difference between those who use SP and those who don’t equals careful design vs casual coding. I cannot recall any serious commercial application that uses in-line SQL. Perhaps real programmers have reasons for that.
goran on December 6, 2007 12:05 PMSjeez, people - Horses for Courses.
A flat out "don't bother with [sprocs|dynamic SQL]" is just being bone headed.
There are situations where it makes sense not to put your SQL in one place.
There are also situations where you'd be mad not to put your SQL in a sproc.
It all depends upon the project environment and requirements.
Will Hughes on December 9, 2007 11:34 AMMy God, is this post still getting responses 3 years after it was written. Right now I hate SQL and stored procs as I've just finished a 600 line procedure to prepare investment portfolios for Capital Gains Tax. The whole project is over a dozen sp's and tonnes of code. Could of been written in half the time/code using java or C# but its the way its done here.
Everything has its place and I've written several sp's that couldn't have been done any other way as efficiently or speedily but this latest project...
nice post...
a good online assembly language guide
http://Assembly.co.nr
This is a great thread!!! I love all of the differing opinions. I tend to sit on the fence between the business aspect and the technical aspect (as any good architect would do). I am not convinced that there is a right way at all. You know what's right? Getting the job down on time, on budget, and it works. I've never been awarded a bonus because my customer opened up the code and said: "I love the way you isolated the different tiers here". And don't talk to me about lower TCO with an embedded SQL vs. SP’s approach. If SP’s are the way to lower TCO, why do DB vendors still support SQL at all? Heck, what version of Oracle are they on? What version of MS SQL? Why not create the uber database paradigm that gets rid of "clunky, embedded SQL" and then show me the independent case study that shows I was able to get rid of 25 developers. Now that's a compelling reason to put things in SP’s. The answer is quite simple...because there is no right answer for all. Why do we (I use the term we) continue to act like we have the answer for all? We’ve only had OUR experiences and that’s it! Even the GODS of IT only have their experiences.
The argument has been made that hiding everything in SP’s is the best solution. And conversely the argument has been made that embedded SQL is not that bad. Let developers write code, not DB logic. Well, in my experience DBA's cost a heck of a lot more than developers. And by using SP’s you've simply moved the problems you all are trying to solve from the application code to the database layer. That's it. And now I have to pay some guy huge bucks to add a column to a table, change the stored procedure's calling parameters, and oh BTW, still have the developer change the code that calls the SP, and put the new attribute on the UI for updating. And that's better? What about the costs and complexity of coordination this has introduced into my schedule having to deal with two different teams? Isn’t that a real cost also? I’ll ask you all this… In your company are there more developers or DBA’s? However, I like SP’s because I can share one piece of code (sp) with an entire database worth of applications. That’s real value. But it CAN be bad…what if one piece of the app needs to access it differently? With embedded SQL, I open up that piece of code and do the inner join I need for that particular query. I’m assured I’m not affecting all of the other lines of code that access the same table. Or do I copy the SP and make a new one that is essentially the same except for a new inner join? Now I have two stored procedures to change if I add a column. I don’t claim to have the answer. I’m just pointing out that you all don’t either.
What if you have a parameter that you are simply increasing the size of (varchar 50 to varchar 100). I've got to not only change the table, but find everywhere that that column is referenced as a calling parm in SP’s, functions, etc... and change it (At least in MSSQL. I really miss Oracle and being able to declare variables as column types of tables). If it was embedded SQL I don't really care. Change the table, and leave the insert statement alone in the code. I'm done.
And what if you have 100's of databases (let's say you’re an ISV and you have one DB per customer because customer's don't really like their data intermingled with other customers). Now the high priced DBA’s have to run the script over, and over, and over, and over again to modify each customer’s database stored procedures interface. All you have done is hardcoded attribute lengths. And the testing argument is crap too. Ok..so the DBA can tell that his stored procedure works just fine. But what if the developer calls the stored procedure with a column that exceeds the length of the calling parameter, or a differing data type? Does the DBA testing help that? No..you still have to test the entire thing… period. But I will concede that isolating this logic helps that problem.
And what if I want to minimize round trips to the database when inserting 1 parent and 20 child records and ship that all as one statement? How do I pass in the 20 child records to the SP? Or do I make a round trip calling a SP for each record? And that helps performance? Uh..how?
And the security argument is bogus. This applies only if you have the need for it and if you log into the database with each user’s account as opposed to using a connection pooling approach.
You get the point. There are pro’s and con’s to each approach. I like SP’s or VIEWS ability to hide complex SQL from the developer. Typically, developers don’t write efficient SQL anyway.
I don’t think that there is a right answer for every situation. I think even inside of one organization there is no right answer. It depends on:
1. The developers ability to write SP’s
2. The availability of DBA’s
3. Volatility of the schema
4. The needs of the application
5. Schedule and budget limitations
6. Large vs. small development efforts
There is no right answer. That's why there is a debate. Do what works for you... Because in three years there will be something new that everyone will hype up as being the end all be all of IT. BTW, whatever happened to network computers? :)
Hi all,
just me 1.5 cents...
Last year I had to speed up a reporting job that had to be done every 3 moth a year.
The original setup was a java programm getting data out of an oracle database, doing some computation, generated pdf reports and wrote back the result for resuse in the next calc cycle. The whole computation for the mentioned three months (using the result of the last run) for about 14000 customers took about three weeks.
BTW. most of the time was spent in the java part not in the database.
My job was to speed up the whole thing. So i just recoded the x000+ lines java code into y00 lines of pl/pgsql on a postgres database and changed the needed lines of embedded sql in the java code for the report generation. I also did every report from the start, not using "previous" runs. The whole thing took about 24 hours on a small box instead of the big boxe where the original computation was done....
That project told me "do the computation where the data is". I am sure on the same "big box" with a database like sybase/mssql or oracle or optimizing the algorithm the computation could have be done within 3 - 6 hours.
Me on January 30, 2008 3:45 AMMy issue with inline sql is that some developers tend to gravitate to it so that they don't have to rely on a DBA or, they simply don't trust a DBA to get the job done or to care about the job that needs to be done.
The company I work for is an Oracle shop. We program in C# against an Oracle back-end. As with any Oracle shop, we have a rather large DBA group who focus their efforts each day on creating and maintaining PL/SQL Packages and Procedures. Their constant tuning and maintenance assures us that our applications are running as good as they can. However, some of our developers do not like to utilize this talent and instead like to completely mimic Oracle Procs within C# code using inline SQL.
My problem with this is that it is a dual effort. Upon release, the DBAs will continue fine tuning and modifying the existing procs that are still used ad-hoc and when schema changes are necessary they will make all the changes to their procs but our C# code will be left behind and thus we will have to play catchup.
I was really surprised when I started here to find that the main developer here was basically taking the "ideas" behind the currently used and maintained procs and then rewriting those within his C# and inline SQL. The outcome of his effort would be similar but not the same as the effort the DBAs did.
When asked why this was being done the answer was "I hate Oracle" and "inline procs ensure portability". I ask "portability from what". Oracle is very expensive and I really don't see the company moving from Oracle to SQL Server any time soon so how does this factor in at all.
This has been irritating enough but one thing that really bothers me is that many of the pro-inline SQL guys here tend to break their SQL apart so that it cleanly displays on the coding screen and this is all well and good for printing but when you search the code looking for what code affects what Tables or Columns, it is very difficult because the Table and/or Column names could easily be broken apart within a string to go to a new line such as:
SELECT * FROM Customer_
Data WHERE Last_Contact
_Date = .....
In the above case, the Table Name is "Customer_Data" but you will never find that in a search, and the Column being referenced is "Last_Contact_Date" but again, you will never find that in a search.
Another thing that irritates me is that inline SQL tends to lead developers to forget about development tiers.... they will just toss inline SQL anywhere. For example, in a web site I maintain, all the inline SQL is in a resource file that is part of the web site... fine, its organized. But, the business tier has inline SQL listed as the values of Constants. Of course, I use the term "business" tier loosely because the web site does a lot of database interaction where it just handles it all on its own... clearly, N-tier development was forgotten here.
That leads me to the conclusion that while inline SQL may have its place, it does tend to lead to lazy, messy code. People tend to forget about layers or tiers of code and think in the moment... this needs to be done and I'm here so there is no better place.
Personally, I prefer all base data interaction to be through procs on the database server, I can live with minimal inline SQL but breaking down incredibly time consuming data processing that is working well in a native proc and creating a slew of inline SQL statements to replicate the proc is only going to lead to sloppy code, confusion, performance decreases and security risks.
Oh yeah, to make matters worse, the web app doesn't do any validation to protect itself from SQL Injection and couple that with inline SQL and you have a nasty mix.
Just my $0.02
BadAsh71 on February 5, 2008 6:36 AMand if BadAsh thinks its bad today, wait until sometime soon when SQL is replaced with "much newness and coolness" of LINQ. Then MS devs will be recreating all the failed EJB-type apps in C#. Its yet another great excuse to avoid tried and tested mature best practices in favour of new toys.
nice post...
Dagestan on February 5, 2008 8:11 AMnice post . shall it continue
http://nurmagomedov.blogspot.com
Jeff, you contradict yourself quite a bit in this blog, but I enjoyed reading it! I follow that you ultimately believe stored procs are a waste of time, or not necessarily if the case is right.
My belief is that if you have the talent to develop procs along with app. code then it is the only way to fly. I have been writing scalable enterprise software for 14 years and have use all major dbs, ORA, MSFT, etc... along with C#, ColdFusion, Java, etc.
Middle or client tier code becomes very legible with proc calls rather than in-line sql and per your pros I think they far outweigh your cons. There are only a couple of languages out there now that truly handle in-line sql coding well. Ever try to write sql statement in C#??? ACCCK! Each line with quotes and underscores, painful.
I am dealing with a project now where a developer doesn't understand the performance ramifications of looping over sql calls at the middle tier upwards of 1000 iterations to retrieve data reflecting parent/child relation in his object creation.
Lack of sql knowledge plagues the industry and this lack of knowledge always makes the client or middle tier solution look bad when there is a performance issue. I rarely see performance issues outside of db integration (i.e. indexes missing and the scenario I described prior) It needs to be understood that knowledge of procedures is paramount to writing scalable applications.
Jeff on February 8, 2008 7:11 AM
I find this attitude all over the place, and it's a bit worrying. It's symptomatic of that greatest of developer weaknesses, the misguided idea that they can do everything. I am a developer with excellent T-SQL skills (if I do say so myself), but I know that my domain is the application, not the database. I write stored procs and then pass them to a DBA to be checked and then added to the database. It also indicates another great developer failing - the idea that the database is simply there to serve you and whatever application you are currently building. Not so. I haven't worked on one project where the database is there simply to serve a single application - there's usually two or more applications, probably being written by different people, that take data from that DB. Having data access tied solely to stored procs has three large benefits:
1. Security. The applications/users do not need to be given direct access to the database itself. They are simply given rights to the stored procedures they need to use.
2. Reduce redundancy. If you have 3 applications connecting to your database, they share some, but not all, of the same calls, and you are using dynamic/embedded sql, you can either create one sql library to hold all db calls, or create 3 seperate ones that share some of the same code. This means you're either shipping code that will never be used, or duplicating the code that is shared. (Proper OO wisdom would actually see you create a shared library, then 3 application specific libraries - quite a lot of work).
3. Maintainability. Surely easier to simply change an sp than dig through your code to find the bit of sql that requires altering, especially since, in sql server 2005, sysdepends will tell you what procedures reference which tables.
Also, if you're using sps, you can make use of nested sps and sql functions to build a dataset from other datasets, using proper, sql-based set functionality. But this is another failing of the developer attempting to interact with the database - they don't really understand how SQL works. In the article above, the author states that 'JavaScript is a giant step up from PL/SQL or T-SQL'. Not true - sql is simply a language with a very specific purpose, but how many times have I seen developers treating it like any normal procedural language? Sql code can be quite elegant, but most developers choose tortured if..elses, and that most heinous of sql crimes, the cursor. Why? Because it's most similar to how they'd use their own language.
The author should also be ashamed that he states: 'Stored Procedures hide business logic' as a criticism. This is a good thing - it's called encapsulation. Your database documentation should tell you what sort of data you can expect to get back from a stored proc. You should never have to look at source code to be able to see how to interact with an API, whether it's a stored proc or a class library - it's bad PROGRAMMING practice, not just bad sql.
The author also states that none of the supposed benefits are true in practice. Sounds to be me like he's never worked in an environment where good database practice has been established. Of course stored procs allow for a better security model - as long as you're actually thinking about security. And yes, they provide a central point of control for data-access, which surely eases maintainability across multiple applications - but if you haven't designed for maintainability, chances are you're not thinking that far ahead.
In short, I'd argue that the author's prime reason for not using stored procedures isn't on some grounds of practice, it's really just that he can't be bothered. Another great crime of the developer.
Another point that I should have made above - if you use stored procedures, users can run them directly from the database in situations where you just want the raw data. This means that your users do not require sql skills, and nor do they table-level permissions - they simply need permissions to the procedure.
Chris on February 21, 2008 6:19 AMHere is the thing it is the only reason I think SP is better.
Lazy half ass programmers tend to copy and paste sql all over the place and do stupid things like that. So when I have to change a SQL block I have to track down and change 5000 locations. Opening another IDE, maybe the one I keep open to test SQL in seems a lot less troublesome....
So SQL is some strange language you can't understand.... wow you must write really good code.
Let me see here... oracle sucks use Sql Server because you can set break points in stored procedures in vs.net with sql server. OMFG
So this sounds like an I hate oracle more than SP sucks
And if your to lazy to do thing well I hope you stop programming because when I have to come work on your half assed buggy code with the same sql in 50 places I'm going to get pissed
YouSoundLazy on March 19, 2008 12:51 PMMy God you people can go for years about this topic.
Its all tools. Evaluate and use what is best that is my suggestion. I have seen dynamic SQL being used in realtime systems that handles more than 325 000 concurrent connections(connection pooling).Log files that grew 500 GB a day. 7 Million users. Over 210 Million database transactions per day. However the stored procedure versions was easier to maintain and performed just as well.
Its like a tool. You dont use a hammer to get a screw in You can do it but it does not make it a good idea. It is what is going to be best to use here that matter. Having inflexible views and ideas is idiotic. Its like saying mine is bigger than yours.
Anyway my 2 cents worth.
Neil on April 3, 2008 6:07 AMi believe that stored procedure is tops it just sound to me that there are a few coders out there that is looking for a short cut to do stuff.if u are not a coder then we will understand why u dont like sql sp's then first get the hang of it.Code first before u comment on sql sp
Stephen on April 14, 2008 8:32 AMI agree with Chris on all the points he has made and YouSoundLazy. It seems that the argument is not necessarily about the use of in line queries or SPs, its basically a question of attitude to the quality of the product they are developing. The first is from the amateur half hour crowd who cannot be bothered to learn how to produce clearly layered, secure, maintainable software, those that think cutting and pasting chunks of code is acceptable. These individuals are, to put it bluntly are the scourge of the software development world. The fact these individuals are typified by the fact that they think having inline SQL queries dotted all around there code is not due to the fact they have think they have real techological advantage, it is the fact they can't be bothered to do the job properly.
The other side are the individuals that think that laying code properly, encapsulating busniness logic, making their applications secure at all levels, and are not selfish enough to think they are the only individuals who may have to pick up the code and run with it. These developers make huge efforts to make the code maintainable, flexible and future proof. They would avoid the use of in-line queries like the plague (if they are able to) because the strategy is, to there way of thinking, just plain wrong. In otherwords these individuals are professional, responsible developers who have a pride in their work and think about the fact that although it takes longer to develop in the initial stages, effort pays dividends when the application needs to be debugged, upgraded or modified, in the coming months and years.
Although sometimes it is not possible to use SPs depending on the DB being used, it is still possible to ensure that your code is structured to allow ease of maintenance by having a predefined DAL and encapsulating business logic.
Anyways is a word, in both America and across the pond. AnywayS here's the proof
= a href="http://dictionary.reference.com/browse/anyways"http://dictionary.reference.com/browse/anyways/a
Just came across this thread, I feel strongly about this so I'll add my vote to the "SPs are your friend" crowd.
I have cleaned up numerous applications that had inline SQL all over the shop. Lazy developers, contractors who come and go, several iterations of the development cycle will do that. A maintenance nightmare, since you invariably come across multiple variations of the same SQL, all with their own flavor. Put it in an SP, call it from the DAL, done.
The portability argument isn't practical in the real world - I'd argue that the vast majority of apps. are developed for a single platform and live on it for the lifetime of the app. But - I recently ported a large Oracle app. to SQL Server (customer tired of paying the premium for Oracle). Thankfully, all data access was in Oracle SPs - MS provides a tool that converted 80-90% of them.
mcair on May 16, 2008 7:45 AMi can't believe this is even a debate. proper use of sp's will optimize the use of the database and simplify a code base. it's that simple. as mentioned before, each database is optimized differently and if you think your app code will run quicker than optimized sp code, you're wrong. i urge any developer working on an app based in oracle to pick up Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions by Tom Kyte. you will instantly become the oracle guru on your app team.
JRock on May 16, 2008 10:21 AMmate - you're f'ing kidding eh? If your stored procs aren't portable that's cos you ain't got the genius to implement generics... never mind fella - you just write inline like the first grader you are...
rissky on May 27, 2008 9:06 AMRan across this and just had to comment too- on the Stored Proc side- especially Oracle. You are nuts to make multiple calls across a network with sql to a database. First- if you are using literals you will kill your performance. The number 1 problem. How many developers run explain plans on their sql? Second- Oracle can pass objects around, take in mulitple parameters, and spit back a dataset- 1 trip across the network. (provided you are not retrieving millions of rows). Try to trouble shoot when multiple apps may hit the same database/table- who knows what code is running. Not everthing should be a stored package/procedure or a function- but it should be used where it makes sense- lots of processing of lots of data. And I have seen some absolutely crazy processing that I can't even imagine being done elsewhere- efficiently.
clp on June 6, 2008 3:20 AMI noticed this old article when looking something up on google. Perhaps a follow up post is needed (have you changed your mind considering the majority of comments are against you).
Putting my 2 cents in I prefer stored procedures especially for business logic which lets face it is mostly where the complex sql comes in.
One reason few seem to mention is the fact I can have multiple interfaces in multiple languages running the same stored procedures. This makes it easier to change an interface to an underlying sql query.
This really helps when you move from a windows app to a web based implementation, all you have to consider is the interface you don't have to go looking around in the old program for what the sql is and copy it meaning if you are maintaining the old app you now have two sql sources to maintain.
My final comment is in my job (work at a college as Management Info Specialist) we have queries that may change each year for the student and course information due to changes in funding and data collections... Stored procedures allow me to send in the year and it performs the correct sql so no streams of if statements in my app code they are hidden in the stored proc where I will know the exact reason for them. I am sure many others have queries that change year on year.
pete on June 10, 2008 2:19 AM“When all you have is a hammer, everything starts looking like a nail”
In my experience, stored procedures are a tool in the toolbox, nothing more, nothing less. The same thing applies to JavaScript, c#, classes, inheritance, interfaces, delegates, user-defined functions, dynamic SQL, polymorphism, XML, YAML, REST, HTML, VBbscript, TDD, refactoring, inline SQL, Mock Objects, rapid prototyping, reflection (need I go on?).
The problem is most developers code solutions based on familiarity, not the right tool for the job. When the going gets tough, many developers just say I know (insert aforementioned tool), therefore the best way to solve this problem is with (insert same aforementioned tool). They forget to stop and ask is there a better way to do this.
Mindflayer on July 2, 2008 10:15 AMMy god, all the mindless anti-SQL FUD being regurgitated! Get over sprocs -- they're not composable, and you *waste* network resources the way they're typically written.
Someone recently implied that dynamic SQL meant literals. Well, that's patently false: the proper dynamic SQL would use parameters even for constants, to increase the usefulness of the query plan (to be honest, this is something a smart server would deal with in parsing, but I digress...).
Many implied that dynamic SQL meant spaghetti code. Also BS: proper dynamic SQL still resides in well-considered places, be they embedded in entities or in a completely seperate DAL.
Security: hogwash -- lock your tables all you want, but provide views, TVFs, and UDFs. Don't kill your app by resorting to non-composable SPROCs. Ad-hoc doesn't mean chaos, nor does it mean giving up even easy security.
You know what? LINQ to SQL supports all of this, and nothing stops any other LINQ to RDBMS provider from doing the same: literals are parameterized, SQL produced is often better than what humans produce (when the provider can prove that the transform is safe -- optimizations that rely on knowledge that doesn't exist in the mapping won't be made), the SQL produced can make use of server-specific features without having to re-write the code...
Absolutely place processing near the data. But sprocs aren't for queries; they're for triggering actions, like well-defined, stable, repeated number crunching loops.
Hide-bound luddites refusing to seriously consider where the actual problems lie (piss-poor code reviews allowing spaghetti to develop) waste my time.
It's amazing how many people here still think it's either SPs or inline queries in every app. Thats what a service layer is for. The service layer speaks sql (or talks to something else that does) and returns nice objects to the business layer, doing validation, security, etc. on the way through.
I'm sick of hearing the security argument too. Apart from a few specific uses (reporting etc, where SPS are probably a good idea) how often do you have different users connecting to the database? If there are multiple projects using a service layer then the service layer account is the only connection. If it's a simple site the the asp.net account is the only one connecting.
Has anyone stopped to consider how an ORM or sql generator would increase performance? Consider something like paging, it would be a pain to have to write this for every possible permutation of a query and theres probably a dozen ways to do it in most DBs.
Suppose we just discovered a faster way to do a paged query with an ORM there is one place to update and all queries from then on will execute the fastest way possible. With SPs you would have to update many thousands of queries, or not.
IMO the absolute best way to increase performance is code reuse (usually through OOP). One optimised function potentially increases the performance in thousands of places.
The rules I follow (can't remember where I first heard this):
1. Never use SPs.
2. Use SPs when theres a very compelling reason to disregard rule 1.
3. Occasionally review exceptions to rule 1. A better way to handle it may have developed.
flukus on July 22, 2008 6:40 AMLINQ o Hibernate for the best.
No SP calls
No sql embedded.
Dynamic SQL code generated for you on the fly by your HighLevel Programming language.
Use it on top of well designed Views.
SP for the Update/Delete/Insert statements, either called manually or integrated in the LINQ or Hibernate Paradigm (which eventually supports SP calls for such statements)
The comments to this entry are closed.
Subscribe in a reader
Subscribe via email
|
|
Traffic Stats |