May 17, 2005
In a recent article, Doug Reilly makes a fairly well reasoned case for the use of stored procedures in lieu of ad-hoc SQL:
So, should you use SPs or ad-hoc SQL? The answer is "it depends." I have placed myself firmly on the side of doing all database access through SPs. I do so knowing that I am not getting any unique security benefits using SPs, knowing that the performance benefits are not as clear cut as I once might have thought (but are still real in some cases), knowing how to leverage SPs to minimize the maintenance load, and understanding that I am more tied to SQL Server than I might be if I were to use ad-hoc SQL. What do you think?
There's excellent followup commentary on his blog entry for this article. In the comments, Frans Bouma immediately links to a formal debate at TheServerSide on the same topic, which he also participated in.
I agree with Doug when he says the answer is "it depends." However, as I've said before, I think it's generally better to err on the side of simplicity whenever possible. Writing a bunch of mindless stored procedures to perform every database operation you think you may need is definitely not what I'd call simple. Parameterized SQL, on the other hand, really is simple. Safe and fast too. I'm certainly not ruling out the use of stored procedures, but to start with procs? That seems like a fairly extreme case of premature optimization to me.
At the risk of repeating myself, I've observed two recurring themes in these discussions that I don't feel are being properly addressed:
- If your primary goal is abstraction, stored procedures are a terrible place to do that.
The idea that you're abstracting away the database (for reasons of access control, coherency, etcetera) by creating a stored procedure "API" is weak at best. Stored procedures only provide the illusion of abstraction. They're incredibly tightly coupled to the database. Make a few changes to the tables and your procs are toast-- just like parameterized SQL. Contrast that with a web service, which provides nearly infinite opportunities for designing an API with access control, abstraction, and decoupling. All accessible from port 80 on any platform, and without the inevitable limitations of your particular vendor's stored procedure implementation and database language.
- Embedding domain-specific languages in your code is a good thing.
Some programmers sneer at the idea of "naked SQL statements clumsily embedded in other languages". This is insane. On the contrary, you should embrace as many domain-specific languages in as much of your code as possible! Use SQL to manipulate set-based data, Regular Expressions to manipulate strings, VB.NET to do COM interop, and C# for bitwise operations. Why in the world would you write a 3-level deep For..Next loop to manipulate a string when you can express that same logic in 12 characters of regex? If anything, we should be railing against the stupidity of being limited to a single, general-purpose language!
Of course, your mileage may vary; every project is different. And always measure actual performance before jumping to any conclusions either way.
Posted by Jeff Atwood
Given the fact that most databases used today are just big boxes filled with crap, I must grudgingly agree with most of your observations. I do however, wish there were decently designed databases that enforced relationships, encapsulated rules and required domains to be specified.
Nice recommendations. I am in the process of doing some sql "stuff" for my thesis and I was contemplating using stored procs to improve performance. The current method of using XML Web Services with native sql is too slow for the massive amounts of data I need to process. So I thought I'd look into SQL Server 2005 which can "apparently" integrate Web Services. How much of this is true is still unknown to me. But thats an interesting approach don't you think?
I think one of the great things about stored procedures that shouldn't be readily discounted is the fact that you can limit a user account to only execute stored procedures and furthermore you can specify exactly which stored procedures the account can execute.
The alternative is to give the account select, insert, delete, and update permissions. And that means that whoever can get access to the SQL user can do whatever he wants instead of whatever you want him to do.
I'm not a DBA so there may be some way to lock down user accounts that I'm not aware of, but this feature trumps all of the supposed performance and code cleanliness arguments. They're gravy in my book.
At the job where I learned SQL, we used stored procedures and triggers a lot. The system included a database core with a suite of applications - some web-based, some unix, some (old) Mac - around it. The apps were written in C, C++, Perl, SmallTalk, and probably a few other languages I've forgotten. AppleScript, too.
The idea there was to embed (much of) the business logic into the database, so that all the applications, wherever hosted, would follow the same logic; also to avoid duplication.
People talked about efficiency, but I never saw any evidence of it, either way.
A final advantage of this approach was the deployment issue. Some of our apps needed to be distributed to users, or users needed to be forced off the system to update an app. When the logic of a stored procedure needed to change, we could just change it, live, and everyone would immediately see the benefit. Or bug.
I was comfortable with this system and these justifications for stored procedures, and I feel that it worked pretty well for us. But as you say, every project is different; I wouldn't expect this approach to apply to all cases.
I was comfortable with this system and these justifications for stored procedures
Sure; back then a web service wasn't an option. But based on your description it'd be a good candidate for that approach today, particularly with all the different platforms.
I still maintain that stored procedures are a terribly limited place to build an API, though.
Web Services with native sql is too slow for the massive amounts of data I need to process
That's understandable. Are you returning lots of rows or just processing a lot of rows to generate smaller result sets?
So I thought I'd look into SQL Server 2005 which can "apparently" integrate Web Services
Interesting, I wasn't aware of that; the feature is called "HTTP Endpoints"
"I think one of the great things about stored procedures that shouldn't be readily discounted is the fact that you can limit a user account to only execute stored procedures and furthermore you can specify exactly which stored procedures the account can execute."
Maybe I am unique but I have not written a program/web page/what ever in the past 10 year where the database built in logins were used for end user authentication and authorization. I have long switched over to the application controlling access and usage and the user never seening the database access accounts or passwords(when needed).
From an end user(exclude dba and dev tools) stand point the only thing I have even seen in that time period are report type tools that allow you to build custom reports and I would not like to have to make the users use stored procedures in thoses tools to access there data ; and thoses accounts are usally limited to read-only.
The problem with using stored procedures in the way you mention is that you don't have the fine granularity that you get with using views and granting select/update/delete to tables or columns as/if needed. With most implementation of SP once you get to the SP you can enter any information as the parameter and if you are doing all CRUD work with SP you have to have SPs for all tables which would allow you to do almost anything you could do with SQL. No real additional security, and administrative wise it is alot easer to handle roles linked to views and tables then all the extra baggage the doing all CRUD work in SP brings.
Egads, I wasn't talking about having actual logins for the users of an app. I was referring to the logins that web apps run under.
I agree with you Bill, but I think I understand what Will is getting at. I've worked on many apps which had both SP and raw SQL interfaces. This is usually necessary because the demands for ad-hoc reporting are too broad and general to be encapsulated in any set of stored procedures.
So what you end up with is, in many ways, the *worst* of both worlds. Raw SQL access to the underlying tables for the developer(s) writing the reporting code, and "abstracted" SP-only access for the developer(s) writing the application itself.
I agree that wrinting "Mindless" SP is NO fun. But i also see the advantages of them.
- Preventing your users from accessing the Tables directly
- "Automatic" Imunity vs SQL-Insertion Attacks
- Precompiled Code
The only problem is writing them. Writing "simple" SP is repetative, mindless and boring work. If anyone objects... Thats your problem. I rater write some software to create those SP from the Metadate that the SQL Server offers me. This is easy once you got the hang of it and it will allow you to create most "basic" SP you need. The SQL-Server knows all the information you need to know to start pumping out those SPs And Automatic generations of SPs like Get_Custumer_Orders isnt that hard. The key here is that you need to properly design the DB. This can save you tons of work later.
I am a lazy Bastard and i see that as a good trait :)
I agree that code generation is a good way to go here. But items #2 and #3 on your list aren't really advantages:
#2 it is possible to write procs that are vulnerable to insertion. If you use parameterized SQL, which you should ALWAYS be doing, you're basically protected from injection attacks. So it's kind of a wash.
#3 parameterized SQL is hashed and compared to a precompiled execution plan after the server has seen that query more than once. All subsequent uses are precompiled. The only disadvantage is slightly more data sent over the wire in the initial request.. eg, "SELECT * .. [500 more chars]" versus "MyStoredProc(param1, param2)"
As for #1 I personally feel procs are a bad place to build access control, but it is a valid use.
I just took a second to read the Article... (So far i only read the Blog) and i must say I didnt know that SQL is also storing an Execution plan.
But i like working with a robust DB. That means a DB that isnt just a loose collection of tables but has some well defined contrains. And thse informations "belong" into the DB for me. I hate doing work twice so you wont find me coding the same contrains on all levels of my application
In number #2 I quoted "automatic" since those SP are also accessed by my (generated) DAL. So far i find it easier to generate then to type by hand. (I really enjoy generating code)
Anyway i like the Article you posted :) And beeing lazy can be a lot of work if its done properly
I'd like to add several clear cut benefits to using stored procs instead of dynamic SQL. I think my points have not been discussed much so far.
The problem of complexity has been solved many years ago. Put simply, Divide and conquer. By dividing any complex (coding) task, it becomes far easier to solve it.
This means that Stored Procs section off your application so that it is easier to test and easier to design. Stored procs provide small logic blocks that can be tested independently of compiled code. This separation of executable code from Database code makes debugging FAR faster and easier.
Just like graphical artists can improve a page because we now separate off the HTML from our actual code, Stored procs allow developers to work on the data side of their apps separately from their executable code. Mixing things, then, is in general, a limiting and bad practice.
If a program generates dynamic or concatenated SQL, you don't really know exactly what SQL is running unless you step through the exact code with the exact values. Sectioning off part of the work into a stored proc keeps things much simpler. It is possible to use the MS SQL profiler to debug dyncamic SQL but it requires DBA level permissions which are nearly always denied to developers.
Another benefit to Stored procs is that SQL is simply terribly messy to embed directly into code pages. At any of the large corporations I've worked at, a single table frequently had over 30 columns; some had 200 columns. Often, insert statements required values from other supporting tables so the SQL statements would be endlessly long. To embed that quantity of logic and SQL into a code page is a real mess.
And, update statements or select statements can take 4 or 5 times as many lines as insert statements.
It's far easier and more maintainable to view and work with SQL separately instead of having it mixed with code.
Also, using stored procs allows different applications and developers to use common database functions. SP's are a simple way to encapsulate common sections of business logic that are easily reusable despite language and platform differences.
I'm not at all convinced that stored procs are any faster for simple insert or select statements though. But in terms of keeping coding more organized, in terms of debugging, in terms of building larger more sophisticated applications, Stored procs are practically essential. If you can't agree with these points, ask most any DBA what he thinks. DBA's work daily with VERY large applications that use heavy stored procs.
As an experiment, lets consider the opposite view to see what value it might have. If we were to remove stored procs from the database entirely in any such large application, the code would become FAR more complex and far more difficult to modify. ANY database change would require searching thru large numbers of pages to make simple SQL statement changes. Only a developer would be able to make the changes and the code would need to be recompiled for every change. I've worked at companies where the lack of stored procs contributed to very messy and confusing code.
When executable code and database code are mixed together so tightly, it encourages a mixture of business logic and database logic.
From there it's only a small step to add in some GUI logic and we're right back in the days of spaghetti coding with a complete lack of separated layers.
A fundamental principle of coding is to use one or two word function names to execute more complex tasks. In the same way, stored procs are like functions that hide more complex tasks. Hiding complexity and dividing up complexity keeps coding simpler.
SQL is a separate language with it's own complexities that are best handled in it's own environment.
In summary, stored procs keep coding and debugging simpler by dividing the work up into layers.
In response to Mark's comment about business/data separation, the correct recommendation for that problem is to separate your code into business classes and data access classes. That means that no business objects (whether persistent or not) should do any data access, and that no data access objects should implement any business logic. This guarantees business/data separation, and simplifies any subsequent modification. Any parameterized queries in your code are found in the data access layer implementation objects. There are many solutions for the lack of clear data/business separation in .NET, but although stored procedures are useful in many ways, they are not a good solution to that particular problem.
A step in the right direction:
The main problems I have seen in .NET web app design and dev in recent years is that ex-ASP and other such people are building n-tier object orientated apps with a relational perspective. They start by getting some loose set of functions that they think the client wants and call them 'requirements'. Then the next thing they do is do the data model and database (for chrissakes). This makes the whole application inherently 'relational', and when the OO programmers come to implement any business logic in C# or VB.NET the object-relational mapping exercise tends to be over complicated, and naturally a lot of logic and behaviour tends to fall into the database and outside of the code. For me, stored procedures in this kind of environment are a danger sign full stop, and one way of getting these people to turn their methodology around, and make the object model prior to the database model (and hence logic naturally falls into the object model and not the db) is simply to ban the use of stored procedures for anything other than report type queries and wotnot. Otherwise its a case of give 'em an inch and they take a mile. It starts of as one simple CRUD sp, and suddenly some db guru has converted into a t-sql neural network to detect market fraud.
Really the dynamic sql vs stored proc debate is good for at least one thing which is to open these people's eyes to the possibilities
What is your take on Microsoft Data Application Blocks?
The way I see it is that, if implementing something is going to require a whole pile of ad-hoc SQL statements to implement a single business operation then it's likely that it's better to implement it as a sproc. The network latency will kill you.
However, in that case, you maybe should look at the database design as well. Sometimes that's the reason why it takes a pile of statements to do something.
In my case it's trees and I work with people whom I shudder at the thought of explaining the nested set model for trees.
stored procedures are not bad. saying they're bad is like saying that guns are bad. stored procedures don't create bad code, people create bad code. I'm fine with you not using them. but please stop spreading rumors about how "almost all stored procedures are bad"
In in-house enterprise applications where one has complete control over the app and database, I "generally" opt for stored procedures.
In commercial retail boxed solutions which need to sit on any type of DB, I "generally" opt for parameterized queries using ANSI SQL. These parameterized queries can be further abstracted out into satellite assemblies, or other type of file (assuming some type of protection/security is implemented), so they can be replaced without recompiling the application, or messing with the database. When it comes to testing the SQL I usually hope for a DB programmer/DBA to give the SQL their stamp of approval, ensuring that even the parameterized queries are scripted uniformly.
Even if the commercial solution is tightly bound to SQL Server as its DB platform, I shy away from SP's because of prying eyes. I could solve that by compiling the sp using "WITH ENCRYPTION", but you better not loose those scripts!
Of course, these are generalizations, and I have to evaluate the options on a case-by-case basis.
This is interesting. Many good arguments made. For us, we try to exercise some restraint when it comes to sp's. Our ideal is to use parameterized SQL in a data access layer.
Re: "As an experiment, lets consider the opposite view to see what value it might have. If we were to remove stored procs from the database entirely in any such large application, the code would become FAR more complex and far more difficult to modify. ANY database change would require searching thru large numbers of pages to make simple SQL statement changes. Only a developer would be able to make the changes and the code would need to be recompiled for every change. "
As a further experiment, let's look at a well-designed app that uses parameterized queries. Tossing out an example of some app that uses SQL coded directly into web pages is a straw man. Any decent app has not only confined SQL to the DAL (as others have mentioned), but organized the SQL into classes for ease of maintenance. For example, in our application, you add a field to the database and add it to the associated business object. That's it. The DAL knows how to load, save, and delete. The business object knows how to enforce business rules. And recompiling isn't a bad thing. You can catch a lot of errors that way! Hacking up a stored proc in a live database looks likes a recipe for disaster to me!
Re: "Egads, I wasn't talking about having actual logins for the users of an app. I was referring to the logins that web apps run under."
If all users run under the same database login (which is usual, in my experience) then you've lost whatever fine-grained control you may have gained by using SPs. Also, who in their right mind is even giving users access to the DB? You can't touch our DB. You can only run the application. You might get a read-only database login with access to a few tables/views for ad-hoc reporting ... if you ask real nice. ;)
Re: "Say you have a client application that issues a SQL Call. You’ve deployed it to 80 desktops. It worked fine for your first 40 users but as more users came online, it resulted in performance metrics that were problematic. You may get lucky and be able to solve the problem by adding an index, but what if the required change is really in the query itself? Maybe you are creating unanticipated locks and the query needs a NOLOCK hint. Then you have to deploy that change to the desktops. If the call were instead to a stored proc, you could optimize the SQL in that procedure and your client apps don’t know about it.
Sure, you could code up the same select statement into the ten applications that the database serves, but this strikes me as a poor reuse model as well as an opportunity to introduce defects 10 times as opposed to once."
Hmmm, I'm not buying the "do it to ease deployment issues" arguement. If you have 80 desktops, I hope you've already got a deployment methodology in place. I'm also tempted to throw in a snide remark about lack of load testing, but I do realize that bugs happen in the real world. However, I'm always cautious when people want to fix them in 5 minutes. How many "5 minute fixes" have turned into all-nighters when you broke more than you fixed?
As for 10 apps on one database, if they're sharing the data they're probably sharing some business logic as well. IMO, code-reuse goes in the application. You *should* be sharing business objects and a DAL, so you *should* still only have one change to make. Now versioning those changes among the 10 apps is another animal - one that SPs doesn't solve either.
I would agree with others here that this is a great conversation. It is a discussion that I have had with numerous folks for over a decade. I will openly admit that I am a defector from the ad-hoc camp and have gone to the stored procedural dark side. There are numerous reasons and frankly they all stem from actual experience.
First, I would like to address the two point raised in your post. Intially I would address your comment “If your primary goal is abstraction, stored procedures are a terrible place to do that”. I simply cannot see any valid backing to that statement. You arguments correctly point out the danger of coupling but not abstraction. Consider abstraction where a framework is capable of being deployed upon say SQL Server or Oracle. Using commonly available objects in the .NET Framework we have demonstrated that the entire business layer can be 100% independent of any direct knowledge of the underlying database platform. That is a very critical form of abstraction. We can write a BL that can be deployed on Oracle or SQL Server without limiting the use of optimized SQL for each platform. That is a huge benefit.
Also, in your statement you mentioned that if the underlying data changes you have to change the stored procedure. Well, embedding ad hoc SQL in an application means the same plus you will have to recompile, build and deploy your application to address the change. The same is not necessarily true of the stored procedure approach precisely because it *can* help you decouple.
Also, I think there is an 800lb gorilla in the room with respect to this argument that nobody wants to discuss. Good developers don’t necessarily write good SQL. In fact, based on over a decade of experience with numbers major consulting firms, those that excel in writing OOP frequently suffer when trying to deal with how database platforms operate. By allowing the use of ad hoc SQL you distribute the SQL to many locations (within the code base) rather than leaving it in the hands of well trained database specialists. Further, the database specialist is limited about what they can do. This is why I have yet to ever encounter a DBA who prefers to have application developers gain direct access to tables.
Another point is that stored procedures simply are more secure and there is really no valid argument against this. Disallowing direct table access ensures that correctly written stored procedures can validate correct visibility to underlying data. Opening the database and exposing the tables lowers your security posture.
Lastly, there is no hope of SQL being generated and emitted from one machine can rival the performance of a well written stored procedure. I have gone through several benchmarking efforts and dynamic SQL never once bested the stored procedure approach. While the difference were rarely large differences the dynamic SQL approach never once came out on top.
Still, I love the debate. I think it will never end.
In many of these discussions, nobody seems to mention the application scope or application environment.
To me, the big difference in the stored procedure pro's and con's (and an addition to the 'it depends' answer) lies in the application scope and it's environment.
If your scope is to support ONE DBMS, ONE customer with ONE application and ONE version, your approach could be one.
If your scope is to support SEVERAL DBMSs, SEVERAL customers with SEVERAL applications and highly versionized, your approach could be quite another. (not to talk about numerious combinations of the latter or the addition of different hosting/deployment environments).
If supporting several DBMSs, your SPs would have to be duplicated to hold each DBMS's syntax. List your own consequences here.
If there exists business logic both in your code (.net, java, php or you-name-it) AND in your SPs, you have business logic in more that one place. List your own consequences here.
If there exists NO business logic in your SPs and instead they are only small clean 'DAL-doors', then their existence are questionable in the first place, why even have them?
Better performance is not only a database issue. A different modeling, normalization or programming approach alone could improve performance.
There are several answers to solving the SQL injection issue. Google it.
Code is for logic. Database is for saving.
Personal opinion: Because solutions should try to be future welcoming, foreseeing and able to solve yet-unknown-issues, then EVEN if my scope is ONE, ONE, ONE, ONE (see above), I still goes the 'without stored procedure' path.
If you're selling software to other companies, you're going to have to support more than one DBMS and leaning on stored procedures is absolutely a bad idea -- no argument from me, there.
And yes, business logic in both the stored procedure and the higher-level code is going to be confusing. Don't forget, though, that keeping business logic entirely in the database is also an option. If you're careful with your database design, you can actually set up a pretty clean MVC separation with the database functioning as the model. It won't scale as far as a app-server system, but it's an awful lot simpler and should be good for a few hundred users.
RE: As a further experiment, let's look at a well-designed app that uses parameterized queries. Tossing out an example of some app that uses SQL coded directly into web pages is a straw man. Any decent app has not only confined SQL to the DAL (as others have mentioned), but organized the SQL into classes for ease of maintenance.
No, it's not a strawman at all. Whether you are embedding the code in an actual web page or in the code in a DAL the argument still holds true, that it's much more difficult to maintain.
RE: For example, in our application, you add a field to the database and add it to the associated business object. That's it.
No, that's not it...
RE: The DAL knows how to load, save, and delete. The business object knows how to enforce business rules.
Only after you tell them how with relation to the new column.
RE: And recompiling isn't a bad thing. You can catch a lot of errors that way! Hacking up a stored proc in a live database looks likes a recipe for disaster to me!
Talk about strawmen! You're assuming that the SQL developer is "hacking up" a stored proc in a live database. The stored procedure would of course be tested before being put in a production environment, but it can be done without a major deployment.
RE: If all users run under the same database login (which is usual, in my experience) then you've lost whatever fine-grained control you may have gained by using SPs.
Not at all. You can easily build in user-specific security in a stored procedure and it doesn't have to be tied to the actual login being used.
RE: Also, who in their right mind is even giving users access to the DB?
Actually, he SPECIFICALLY said that he wasn't talking about individual logins. Also, security isn't always about users. Do you think that developers or other internal personnel are never an issue when it comes to security? I can't count the number of times that I've seen a config file out on the web server with a SQL username and password sitting there in plain text for anyone with access to see. If security is tied to a Windows login then any developer with access to that login can log directly into the SQL Server and delete all of the rows of an entire table. "Whoops! I was just trying to fix a record!"
RE: Hmmm, I'm not buying the "do it to ease deployment issues" arguement.
Just because you don't buy it doesn't mean it's not true.
RE: If you have 80 desktops, I hope you've already got a deployment methodology in place.
Sure, and how many of those methodologies require that users reboot their machines or exit the application or wait for an overnight (or worse, weekend) release? How many require a reboot of a web server or similar?
RE: However, I'm always cautious when people want to fix them in 5 minutes. How many "5 minute fixes" have turned into all-nighters when you broke more than you fixed?
Yet another strawman (from someone who actually complained about strawmen arguments!). He didn't say anything about trying to do a 5-minute fix.
One more thing about stored procedures. It would be nice if there was a stored procedure that could, with one shot, check the syntax of each and every stored procedure inside a database. The syntax should check for field validity. If any fieldname is changed in the scenario of thousands of stored procedure, not only are SP's going to break, but automated maintainance processes that copy databases to backups will also fail because SQL verifies that all SP's are correct before executing a DB copy or backup.
If the database schema changes then using an API is not really going to give you abstraction you still need to modify your API to take into count the new schema changes.
I totally agree with Mark Tennenhouse on October 11, 2005 07:55 PM
Divide and conquer. Let the technology do what it was designed to do. Mixing code platforms only creates more unnecessary complexity...
Just because you can, doesn't mean you should
Who wrote this crap!?
Obviously, I think we are missing the points of both stored procedures and raw SQL. Both have their own advantages.
As previously said raw SQL is a great solution for parameterized reporting and more complicated DB tasks(db backup, restores, table creation, database definition, etc.) Really, I would say anything which can not be automated easily, should be written in raw SQL. Since automation is difficult, more than likely so will control. This usually is a sign of inconsistency at one or more levels in the database, whether security, data, structure, or flow of control.
Stored procedures were created to place limitations on code/programs. Limits such as the number of recompiles, limitations on the executing code(if designed correctly you always know what an stored procedure is going to do, no exceptions), limits on the applied database security(keyword is applied not fundamental),and limits on the usage and redundancy(more consistency in your coding).
All in all the correct usage of these fall in the hand of us as IT Professionals. We must first know the advantages/reasons for which processes, such as stored procedures, were invented to control. Then, naturally the power to use each in the way intended is going to be a decision which ultimately falls into the hands of not the code or stored procedure, but into the hands of us as IT professionals.
The aim of an argument or discussion should not be victory, but progress.
- Joseph Joubert
Here's a good reason to use stored procs when using VB (vs. C#):
VB's god-awful string concatenation versus C#'s @'string' construct. VB makes me choose between nicely formatted sql code or LOTS of _
People talked about CRUD sps. Anti-SPers cite how boring and mindless they are to write. Pro-SPers counter with how you can use code generators to create them. However, one of the purposes, major purpose IMO, of SP is to hide database schema from application logic. So if SPs are used correctly, there should not be so many CRUD SPs at all, let alone 400+ or 600+ CRUD SPs for a 100 table database.
I believe both solutions have merit. I think that it is better not to decide One Way Or The Other Forever, but rather choose the best fitting solution for your expertise, team, environment, and problem space. What follows is my attempt at a check list for evaluating what approach is most likely to best fit your needs. Make a list with two columns, one for stored procedures, one for parameterized SQL, and add checks based on the list below. When you're finished, the column with the most checks would be my recommendation based on your specific situation and skill set.
The Rules of Thumb in the Stored Procedures vs Parameterized SQL Debate:
1. If performance is a primary concern, and you must limit connections to the bare minimum possible and/or every byte is precious, add a check for stored procedures.
2. If application flexibility is far more important than performance, add a check for parameterized SQL.
3. If your team is highly compartmentalized, composed of smaller sub-teams of database experts and application experts add a check for stored procedures.
4. If your team is composed almost entirely of application developers who are much more comfortable with OOP code than the database, add a check for parameterized sql.
5. If your application must support many different database products or the specific database product being used will not be known, add a check for parameterized SQL.
6. If all your database products are known and are not likely to change, add a check for stored procedures.
7. If it is extremely important to limit user access to important data, or your database contains highly sensitive data, add a check for stored procedures.
8. If your application does not contain both important data and mundane data in the same table, or contains no highly sensitive data at all, add a check for parameterized sql.
9. If your app development team is highly experienced with data layers and exclusively controls the database connection tier in your application, and the database team is inexperienced or does not exist, add a check for parameterized sql.
10. If you are creating or support an application where other teams with far less experience will be creating their own data access tier or access tier elements and you have a skilled database expert or team of experts to act as SQL gatekeepers, add a check for stored procedures.
11. If you occasionally or commonly require a large string of SQL queries or JOINs to accomplish a needed application task in the database, or your tables often have large numbers of columns that must be dealt with in a query, add a check for stored procedures.
12. If your queries are limited almost exclusively to simple single line insert/update/delete/select statements against a single table, add a check for parameterized SQL.
13. If you're working with an inexperienced team that does not understand SQL based attacks such as SQL injection and/or you cannot be sure that all members will write proper parameterized SQL, add a check for stored procedures.
14. If your team is composed of experienced app developers who have little experience with database design and optimization, add a check for parameterized SQL.
15. If your application environment combines both transactional and business intelligence operations, add a check for parameterized SQL.
16. If your application deals purely with a transactional database, add a check for stored procedures.
Hopefully you find this list helpful, perhaps in some small way it will help calm the debate and allow people to realize that there is no particular One Right Way in this debate, but two rather equal options.
From my experience using stored procedures is the only way to go. Performance aside most programmers simply don't know anything about parameterized sql. It always ends up being sql string concat which is very very bad ummmkay. Stored procs allow you to change logic in the database with no re-dployment. You can change the proc and users are unaffected, assuming there are no bugs in there ;). Use ANSI SQL for your procs and you can port to any database you want. And I believe someone else said it but you can lock your DB down tighter this way. Just give a user execute to whatever procs you want and no one needs direct table access. Yes you might have a lot of procs because of this but I would rather clutter the DB then clutter the code with that stuff.
Just for those claiming that sprocs *must* be used at the 'enterprise' level; you are shamelessly wrong. The answer is that 'it depends' -- development is fundamentally an engineering exercise balancing requirements and constraints. 'Enterprise' application is not a requirement; its a label with little value. The requirements that have value have already been enumerated above: performance, security, etc.
I personally lean more to the parameterized sql approach, because re-using business components at an application level has proved more useful in our domain over a number of products than trying to re-use sproces--throw in multiple RDBMS support and sprocs become insanely expensive to maintain. Echoing the author's statement-- the vendor specific data layer is a *terrible* place to try begin abstractions. The performance argument is negligable at best in most circumstances.
Thanks for everyone who posted. As a MSSQL DBA I agree that 'it depends'. I dont necessarily want to keep up with an sp for every simple single table SELECT query but relying on developers to write optimized sql is not feasable. This is NOT a critique of developers as I have written my share of web apps (pre .net), but it is VERY true that you can write a SQL statement in many different ways. It is not usually apparent which way is going to be the best until you get some performance metrics from the DBMS. Query performance depends on many factors including indexes on tables, Database Structure and such.
If you have a good DBA team, SPs can help everyone. It speeds up development time and allows performance metrics to be analyzed over time. When the DBA finds an underperforming SP it is easy to optimize, test changes and implement the change in the DBMS improving performance without any recompiles or developers performing application changes.
I'd rather debug an app that passed parameters to an SP than one that has tons of complex parameterized sql. I've seen queries executed against the database that caused blocking and needed optimization and it took the developers a lot of hours to find the location of the embedded sql in the source code. If it were in an SP it would have taken much less time.
Layering security is very cool. The .net framework has enhances security features but putting all of your security in one layer doesnt make much sense. Using the HTTP Endpoints to access SPs that have explicit permissions on the Stored Procedures instead of granting permissions directly to the tables adds another layer of security for the app and database against mistakes in coding (were only human) and gives the DBA the ability to enhance the performance of the database as well as securing it properly.