Pingback from StackOverflow: http://stackoverflow.com/questions/216569/are-the-days-of-the-stored-procedure-numbered
I couldnât disagree more (at least for SQL Server)
I have seen massive performance differences. Procedures can be cached (heck, there is even a dedicated procedure cache).
There are huge performance hits. Parsing and execution plants are just one hit. I run about 500 concurrent users and even a .5 second speed improvement for a common process can lead to hours of saved CPU time per day.
Also - I find maintenance easier and it DOES integrate with VS (it even did back in 2004) so I canât see an advantage in NOT using procedures.
No - hold that thought. If you are writting a small one-off application that is never actually going to be run live, then sure. Otherwise, I simply wouldnât use embeded code (not evel LinQ - read up the performance impact of that over-hyped technology).
Philip, I believe when you use sp_ExecuteSql for dynamic sql it also creates and caches an execution plan. Thereâs a good chapter in this book (http://www.microsoft.com/MSPress/books/8564.aspx) about dynamic sql and how in some cases itâs faster than stored procedures.
It seems like that the writer has learned a new technique Web Service and just as a newbie trying to use it anywhere he can he is refusing to accept the stored procedure and is blindly advocating the use of Web Services. I behaved same when I was 18. Jeff forgets 1 thingâŚnot all client apps developed by many different developers may utilize a web service. Not everyone is doing development in VS.NET
Someone should take a look at the Code Image⌠Only a fool can prefer inline code for this.
I think some of you are picking on SPs because someone shoved business logic in there. Business logic is supposed to be its own layer folks.
Declaring using SPs a dubious practice because people arenât using them correctly is like saying guns are bad because they shoot people. Just put the weapon down and back away slowly if you donât know how to use it.
Wow, long read. I support the moderate voices here. (And the blog authorâs quixotic spirit at the time). I love SQL and MS SQL Server. And yet when permitted⌠Iâve had great success with dynamically compiled sql. I put it in a DAL so itâs easy to find. I generate the sql for most of my procs/udf with C# so theyâre easy to maintain. (Not so easy to pitch the homegrown solution now that we have Linq). I have no philosophical issues that require my DAL to be dumb about persistence, and since my clients arenât made of money, I donât think I could advise my client that itâs a mustâŚ
I always use a proc when a lot of data needs to flow across several steps - situations where you might use a temporary table-type variable for large intermediate results and in the end return a relatively small amount of data to the middle tier.
Way back when, mandatory crud procs was the common wisdom. For all the reasons mentioned by the free thinkers, itâs not so dogmatic anymore but itâs still hard to argue against a zealot with a list of undated material to back up their rhetoric, superstition and ego.
I have seen abuse of inline SQL. Especially with recursive calls⌠Even recently, a large corporation had amazing engineering in many areas but their DAL consisted of a couple of methods that took a concatenated string of sql and returned an untyped dataset, (with no compile). They didnât care. That was most likely politics, but reading through the comments, I guess smartly normalized tables went out of fashion for awhile?
At any rate, itâs nice to see MS remove the web service from SQL Server. Itâs productive to have a full featured db on tap. Oslo is making no apologies.
I donât understand the whole make it a web service deal. The examples above all talk about VB with dynamic SQL versus VB with SPs. If Iâm running a VB app then I already have a connection to the database. I am presumably inside a transaction. Now instead of reusing an existing resource to access the database, you want me to open a socket and call a web service? There are several issues here:
- That web service call is outside my transaction so I canât roll it back should something downstream require a rollback. REST and transactions donât really mix.
- That web service needs to open a database connection and rerun all the authentication code Iâve already run when the client logged in. If there are several calls to the abstraction layer, there are several database connections.
- My deployment is now more complex. Before I had a client application and a database server. Now Iâve added an http server to the requirements.
Jeffâs argument that SPs are not the only way to section off the database through APIs. Thatâs true. MQ services and CORBA calls are also ways to do this. They benefit though from maintaining transactional integrity through the use of transaction managers. REST-based web services are the antithesis of transactional programming.
learn to use database! please!
I have worked on projects architected from both perspectives, and have come to the conclusion that:
-
Using SPs for CRUD is insane. If you do this, most schema changes require changing the SP interfacesâŚwhen this happens developers will choose not to change the schema because it is too much trouble. Instead in many cases they will choose to overload fields, make use of Table1.ExtraColumn1, Table1.ExtraColumn2, etcâŚrefactoring a schema in any useful way is a huge PITA. Making refactoring harder makes refactoring not happen.
-
Using a normal language like Java or C# to do reporting is usually way too much coding and the result is extremely slow reports. This is one case where business logic will usually end up getting duplicated in the middle tier and data tier as reports will need to do calculations that the middle tier also has to do (like sales tax calculations for example).
-
The security argument in favor of using SPs for everything doesnât hold water. Any security setup that you have can be implemented with a proper application of table-based security with roles and views. This is only even necessary if you have to have rock-solid database security. 90% of the time you can get by with security implemented in the middle tier. If you are paranoid about this, just stick your middle tier behind a trusted authentication wall (a physical application server) which has access to the database.
-
Business logic is much easier to implement in a real OO language than in T-Sql, Pl-Sql, or whatever.
-
With a modern ORM, you can detect schema changes and get compile-time errors when a field is renamed, a table is renamed, field made not-nullable, etcâŚtry doing this with SPs that can exist not only on one server, but may be spread all over the place using linked server connections. Bottom line: if you rely on SPs for business logic, changes are HARD and very error-prone.
-
Version controlâŚTFS has some decent database version control capabilities, but TFS is very heavyweight and expensive. In general, keeping your code in actual code files saves a lot of time when you need to roll back a change.
-
Unit testing is much easier when your business logic is contained in an actual class where dependencies can be mocked. You cannot do this with SPsâŚthe best you could do is some awkward test database configuration which only one developer can use at a time if this database is on a centralized server.
-
If you use SPs for your business logic, all the dependencies for this business logic had better exist in the database! Need to check the results of a web service to validate some input? Have fun with that using T-Sql. Want to validate input without putting it in the database first? Try doing this in an SP for the rule: An new order must have at least one OrderItem.
Like I said, I have seen both sides of this and can tell you with certainty that a standard business application is much more maintainable and flexible with business logic in a real middle tier. I have seen no good use for SPs with the exception of reporting and mass, complicated data manipulations.
Mike
Database engines are designed to be able to process sets of data quickly and efficiently and compile Stored Procedures for this very purpose. MS best practices also recommend the use of Procs wherever possible for reasons of performance, security and data abstraction. I work as a DBA in a development environment and every sane developer I have met firmly agrees that SPs are the way to go. Like everything in IT there is more than 1 way to achieve the same end result but some ways are most definitely better than others.
This is like debating the value of mineral deposits to a tree. The true reality is that for the majority of development environments it would be fine to put that tiny 500 row CMS database in a csv and access it with a text driver. Lots of OO developers contributing to this have neverâŚ
- Tuned a highly concurrent RDBMS.
- Agonized over multi-path statistics optimizations on a VLDB.
- Understand what index optimization is.
And even if you do know how to write explicitly parametrized dynamic calls in the middle tier and want to show off your new-found skills, what about your colleges? What about that new junior developer that I have to deal with on a daily basis? What about the normal lazy sloth that just wants to spit out code as fast as possible so they donât miss Greyâs Anatomy? Son, we live in a world that has walls, and those walls have to be guarded by men with guns. Whose gonna do it? You?
So will Yukon help with any of these problems?
Paul:
â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?â
Try Edit-Find And Replace-Find In Files
Jeff:
This has been a good read for me. I enjoy hearing both sides of the argument and really havenât had a strong opinion either way. Until now.
I believe the security and abstraction that comes with using stored procedures make them well worth implementing. I know the maintenance aspects have really saved my team on a number of occasions.
Security wise, preventing direct access to the tables make sense. It keeps them from being abused by the general public (or casual developer). Stored procedures create a standard interface that can be enforced, keeping an application developer from unknowingly putting your data into an invalid state.
Although itâs bad practice, stored procedures can be modified to add functionality to a deployed application without re-release. This makes my boss happy because he doesnât like the time or the process involved in certifying and releasing builds. A stored procedure change can often take less than a few minutes and get an application back up and running. It can also add new functionality to a well designed application.
As far as being able to swap out databases, I think you can still do this with a small amount of effort. If your database supports stored procedures, youâll have to re-code them in the new database. One plus to note here is that your code should require NO changes to support this. I should say âin theoryâ but this has been the case for me on almost every occasion so far. If the new database doesnât support stored procedures (mySql) then you can override your standard datalayer (that interfaces to the stored procedures) and put the logic in there. A word of experience here: it takes a lot more effort to implement logic from a stored procedure in your data layer. One plus of database script languages is that theyâre designed specifically to manipulate data. Theyâre very compact and focus primarily on that function.
Think 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.
If youâve never tried stored procedures, you owe it to yourself to do so and formulate an opinion based on your experience. Do some more reading on the subject too. Quite often, youâll find that a development method/practice doesnât make sense because you havenât used it in the same way that other people have successfully used it.
You guys are clueless if you think that stored procedures have no real world benefit. 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. You use stored procedures to present an API for a schema, and get the benefit of faster execution, security, etc.
Dynamic SQL queries are an absolute killer in terms of database performance. They do not scale, at all. When your users are screaming because your query is taking 20+ seconds to generate output, 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. They are also a killer when it comes to maintaining consistency for client applcations, which may not always be your simple compiled application. 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. Otherwise when you change the schema under them, they are screwed. And so are all your users using that dirty code.
Itâs nice to bitch about how much of a pain in the ass they are (and I agree - they are), but if youâre that vehement about how useless they are it would seem you donât really know pain yet.
I have to askâŚwhy is everyone saying that the alternative to stored procedures is SQL? Has anyone heard of an ORM? I mean, seriously, this is 2006.
For thos .NET people, check out nHibernate, or DataSets directly from MS. For all you open source people (re: java), I am sure you have heard of Hibernate and are probably using it because you donât have you head up your butt!
It seems that this stored procedure debate only happens with .NET people. Why is that? This issue has been settled for years on every other platform. it would be nice if .NET people would join the rest of hte world.
Sorry , i respect your right to your point of view butâŚ
your mad.
I 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.
If your supporting more than 1 database environment, donât go the stored procedure route because you will need 2 sets of procedures, one for database A and one for database B. This will be hard to maintain and a pain in general. Plus right now just two, what happens when another database is added, now you need 3 sets.
Create a DAL (Interface) that each database layer will implement
How you implement the DAL is up to you.