Don't use stored procedures yet? Must be suffering from NIHS (Not Invented Here Syndrome)

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’.

 

The following day I usually receive some email from people that attended the talk – it’s always great to hear from people and how they build their applications. Every once in a while I’ll get a comment such as: ‘I don’t use stored procedures for several reasons, one of which is that it ties me to a particular database’.  I just finished answering several such emails after our last couple of roadshows events and thought – great blog topic!

 

As soon as you put data in a database, whether its SQL Server, Oracle, or DB2, you just became tied to that particular database — surprised??? Sure, it’s just your data that is in there and you can use ANSI SQL for ‘generic/independent’ access to the data from your application. But claiming that ad-hoc SQL (SQL script embedded directly in your code) is the only way to accomplish database independence is simply incorrect. In fact there are many ways to efficiently solve this problem without creating such a brittle link between the application and the database.

 

One of the ways we solve this problem in ASP.NET “Whidbey” and the ASP.NET Forums (and now .Text and DotNetNuke too) is through the provider model pattern: a pluggable data access layer component that can easily be swapped out for other data access layer components. The difference is we do everything possible to take advantage of the database we’re running on. This means ensuring that all data access is through stored procedures and that the stored procedures return data to the business logic layer with no wasted cycles and no wasted data, i.e. sort the data in the database and only return records which will be used, no-more no-less.

 

There are several reasons for using stored procedures to access data, below I’ve attempted to capture the 3 that I always reference:

 

1. Ad-hoc SQL Script is brittle

You would never put business logic code in your presentation tier, right? Why put ad-hoc SQL in your business logic layer? Embedded SQL script is very brittle; small changes to the database can have severe impacts on the application. Whereas stored procedures afford abstraction between the data and the business logic layer. The data model can be dramatically changed, but the stored procedures can still return identical data.

 

2. Security

What permissions are required for working with your data? If embedded SQL is used it likely means that the application can execute any INSERT, UPDATE, DELETE, SELECT script it desires. You wouldn’t – hopefully you don’t – run as the administrator (sa) account on your server, so why allow full access to the database itself?

 

Stored procedures allow for better data protection by controlling how the data is accessed. By granting a database login EXECUTE permissions on stored procedures you can specify limited actions to the application. Additionally, stored procedures are a counter-measure to dangerous SQL Script injection attacks, a susceptibility that applications using embedded SQL are more vulnerable to.

 

3. Performance

A counter argument for performing data manipulation in a stored procedure, vs. simply retrieving the desired records in a SELECT statement, is it slows SQL Server down. That’s not exactly correct. In many cases you can get better performance by looping and filtering data in SQL Server than you could performing the same loops and filters in the Data Access Layer – databases are intrinsically designed to do this, while you and I have to write our own code (which do you think is going to be faster?). It is, however, important to understand how SQL Server uses indexes and clustered indexes. I’d recommend SQL Server 2000 Performance and Optimization Tuning to learn more about this and other SQL Server performance guidelines.

 

Beyond sorting and filtering data in the stored procedures you can also batch common work together or retrieve multiple sets of data. For example, retrieve some data, update a datetime stamp, and then insert a new record. If you were to execute these 3 tasks once a second as ad-hoc SQL this would result in 259,200/day independent database request vs. 86,400/day if all were encapsulated in a stored procedure. That’s 172,800 database connections and network IO usages that you no longer require! Consolidating work through stored procedures makes more effective use of a connection (and your system).

 

There are also internal performance benefits to SQL Server for using stored procedures vs. ad-hoc SQL script. When stored procedures are used SQL Server can cache the ‘execution plan’ that it uses to execute the SQL vs. having to recalculate the execution plan on each request – would you recompile your business logic class on each request? …probably not. While small details such as this appear to be trivial, when you start considering total requests/second that your SQL Server is serving, small performance improvements to repetitive code paths add up very quickly and make your server more efficient and scalable.

 

It’s also worth mentioning that the easiest way to get performance out of your database is to do everything you can to take advantage of the platform you are running on. For example, if I were to write an application that used a DB2 database, I would do everything I could to get the maximum performance out of that system vs. trying to write generic data access code. Why? Well the SQL Server team here at Microsoft has some of the best minds in the industry making sure SQL Server has phenomenal performance and scalability – it would be a shame not to take advantage of all that effort, right? We can assume that other database companies hire smart people for their products too — don’t get striken by N.I.H.S. (Not Invented Here S< /STRONG>yndrome) a dreaded&n
bsp;software developer disease where nothing is good unless you wrote every line of code!

 

Side-note: if you jumped to the conclusion that the point of all this is to advocate all business logic residing in stored procedures, you missed the point. There needs to be balance. My general rule of thumb is: return only the necessary data, no-more no less. The more efficient you can make your data access code by rapidly opening and closing the connection to the database the better; stored procedures provide you with an effective tool for accomplishing this. Performing business logic is frowned upon, i.e. transforming the data or performing calculations on the data. Use the power of the database to thresh the wheat from the chaff. Use your business logic to turn the wheat into dough 😉

 

To follow this discussion in whole:

Read: this, this, and this.

 

[listening to: John Mayer, Comfortable]

 

Edit: 11/18

A couple of people have very correctly pointed out two things:

1. I stated that SQL can ‘pre-compile’ the execution plan. I was using ‘pre-compile’ interchangably with ‘cache’ (the original statement said ‘pre-compile or cache’. SQL Server does not have the ability to pre-compile a stored procedure – I edited the article to remove the work ‘pre-compile’ as it can be misleading.

2. Stored procedures are not the only defense against SQL Script injection attacks. Parameterized ad-hoc SQL works just as well, although stated in the original post.

 

Edit 11/19

Added details about entire conversation


49 Comments

  1. If you use parameterized queries instead of strictly ad-hoc sql statements, there is no performance benefit in using stored procedures, at least in Sql Server 2000.

    Sql Server 2000 caches parameterized queries as well as stored procedures.

  2. Bravo. Amen. Exactly the mantra I preach in my data access classes and every time I talk on data access. Great summary of the reasons. Glad to my same convictions re-affirmed by people who know a lot more than I do.

  3. too funny. i have seen nihs in action. if it isn’t stored procedures it would be whether or not you put a curly brace following your for statement on the same line or directly beneath it.

    -Mathew Nolton

  4. Thanks Rob,

    Nice post.

    In regards to NIHS (pronouced like nice, but it isn’t) : Not having invented a database, they should be using text files.

    The truth doesn’t change, it just gets expressed differently to different people at different times.

  5. I love stored procedures for all the reasons you list, and couldn’t really imagine writing data-driven applications without them. Usually there are trade-offs when you choose one development technique over another, but as far I’m concerned when it comes to using stored procedures there is NO downside. Applications can be build more secuirely, are easier to maintain, and typically perform better.

  6. You are completely missing the point.

    The main bottleneck in our industry is programmer productivity. When you built ASP.NET you were not thinking in having the best performant platform but the most powerful and easy to use. Developers are giving up performance for productivity. The CLR is about that.

    Stored procedures are hard to debug, hard to deploy, unflexible (try building a parameterized query system with stored procedures), and bad for a lot of operations (try encrypting or doing string handling).

    Granted, CLR stored procedures will make this easier, and it’s a step in the right direction.

    For security, the way to go in windows is COM+ with integrated security. It’s the most secure way, there are not connection strings, etc.

    By leading MS developers to use stored procs, you are leading them to Java, where the standard practice is not to use stored procedures, which is much easier. Of course you could lie to yourself and think that Java apps don’t scale or don’t perform, but even if that’s true, it does not seem to have impact in Java adoption.

    Is PHP scalable and performant? Do PHP users care?

  7. Peter –

    > not thinking in having the best performant platform

    I’m obviously a little biased here <g> but ASP.NET is the best performing platform.

    > hard to debug, hard to deploy, unflexible

    Yes and much of this is being addressed in SQL Server ‘Yukon’ which allows you to run CLR code in the database. But just because something is diffcult is not an excuse, especially when the benefit outweighs the cost. For example, I hate going to the dentist – it usually hurts; takes up more time than I want to give up; and sometimes I have to pay money for all of this ‘fun’! Does that mean I shouldn’t go?

    > bad for a lot of operations

    Exactly. They weren’t designed for encrypting or string handling. They are designed for working with data. Believe it or not they are exceptionally good at doing it too <g>. I think if you read through the entire post (or at least the last paragraph) this is exactly what I said.

    > leading them to Java

    Is this a quote from star wars? From anger comes fear, from fear comes… I’m still trying to parse this 😉

  8. "There are also internal performance benefits to SQL Server for using stored procedures vs. ad-hoc SQL script. When stored procedures are used SQL Server can cache or pre-compile the ‘execution plan’ that it uses to execute the SQL vs. having to recalculate the execution plan on each request – would you recompile your business logic class on each request? "

    This is bull. SQLServer does not precompile stored procedures. It keeps the execution plan for ad-hoc queries AND stored procedures in the cache, there is NO difference between the two. The only advantage a stored procedure CAN have in this regard is that a stored procedure has a solid signature, thus the execution plan is easier found in the cache. The execution plan for the ad-hoc query CAN take a slighter longer time to find back. If you don’t believe me, check out the BOL of SqlServer ("Execution Plan Caching and Reuse".

    The reason SQLServer does this is because the optimizer at runtime can now optimize the query based on statistics that are very recent.

    It’s always a good laugh to see people defend stored procedures for SqlServer and using the "they’re precompiled" argument. They’re not. And in a lot of situations, they’re slower as well, simply because you have to code your stored procedure to handle optional arguments because the WHERE predicates in a SELECT query inside a stored proc have to be flexible: e.g. you have to be able to filter on 1, 2, 3, or maybe 4 fields and all combinations. Good luck defining those.

    And no, that’s not exceptional. That kind of queries are common.

  9. The problem with Stored Procedures is that they need rewriting for each database, most SQL statement can be written so that works on all important rdbms. As an ISV we have customers with both SQL server and Oracle, next week a sales person may sell our software to someone with DB2!

    All of our programmers can cope with writing simple SQL statements, should we also make them learn TSQL and PLSQL, and whatever DB2 uses?

    I have found that the speed up from Stored Procedures is not as great as then what I can get by spending the time doing a bit of performance work with ANTS (a very nice profiler) on the complete system. We DO have 2 Stored Procedures, as the profiling showed that Data Access was the bottle neck in that case, and we could not solve it by rewriting the SQL.

    Now if you provided a TYPE SAFE, database in-depended way of calling store procedures, and a compilers that took my SQL and converted it into Stored Procedures I would be interested. But why not just provide “Imbedded” SQL that is processed by a pre-processor before the C# compiler, like SQL was done with ‘C’ before ODBC made everything so hard. The pre-processor’s could also be pointed at a database, so would give an error if I misspelled the name of a columns at COMPILE TYPE, in these “advanced” days, I do not get a error when a misspelled the name of a column or stored proc until RUN TIME, that is not very good…

    If I was writing “in house” software then never needed to be sold to a customer, I would use stored procedures. Most software in the world is “in house” single company but not all.

    Email: ringi at Bigfoot dot com

  10. I think this post has a good summary on how SQL Server 2000 really works. The main point is that parameterized queries are just the same as sprocs when it comes to speed as both are compiled on first use and the results are cached using a signature of the query.

    http://groups.google.de/groups?hl=de&lr=&ie=UTF-8&oe=UTF-8&selm=OMSaLMBEBHA.1412%40tkmsftngp02

    Your argument in the second paragraph of 3. Performance is only true if you really plan to issue several calls to SqlCommand when you really could use one. You can do anything a Sproc can do inside a single SqlCommand so there won’t necessarily be any overhead due to the number of calls.

    Until Yukon arrives for the broad public, Sprocs have severe problems with versioning and integration in the IDE. Also, deployment can be a nightmare. I agree with Peter that this comes down to reduced efficiency for a programmer which will in most cases outweight any real or perceived speed advantages/disadvantages. IMHO, the only real reason to use them is code-separation in n-tier developments, but even this is highly debatable.

  11. "Well the SQL Server team here at Microsoft has some of the best minds in the industry making sure SQL Server has phenomenal performance and scalability – it would be a shame not to take advantage of all that effort, right?"

    Yes, great suggestion, Rob 🙂 😉 So why don’t you use the hard work of those great minds by using their runtime optimizer which works with cached execution plans for every query executed, stored proc or ad-hoc. 🙂 You simply use the stored proc route while they put so much work in the optimizer to make it work at runtime more efficient, thus not with pre-compiled code, but with optimized code which is constructed at runtime and cached in execution plans (and recompiled when necessary (i.e.: when recompilation is taken less time than looking up the cached execution plan). 🙂

    Sorry, couldn’t resist. 🙂

    Rob, please note that you ruined a lot of hard work for a lot of developers: now still a lot of people think stored procedures are the only alternative and will KEEP ON demanding everything that access tables (or views! how about views 🙂 ) is written in stored procedures because someone from MS said so, even using false arguments.

    Keep that in mind next time, thanks.

  12. Sorry, Rob – this whole post is so terribly bad that I am sorry for anyone who ever follows it. I strongly suggest you read the comments here and Frans excellent blog post going into great detail about all your points.

    NONE of them holds against an examination of facts.

  13. Frans & Thomas, in my opinion, you are incorrect. Rob makes a lot of good points and my experiences cause my to side with Rob.

    Wally

  14. ::Frans & Thomas, in my opinion, you are

    ::incorrect.

    While you are entitled to your opinion, Ivalue it with the arguments you give: zero. Frans put up a lot of good arguments – do you ahve any to counter them?

    ::Rob makes a lot of good points and my

    ::experiences cause my to side with Rob.

    Rob makes a lot of INVALID points. Like the whole caching thing. It is technically incorrect.

    One can argue about architecture etc., but one can not argue with arguments that are simply wrong even according to the documentation.

    Do you live in your own world, where your own SQL Server does not behave like everyone else’? Because otherwise, at least SOME arguments are just wrong.

    And for the rest, I suggest strongly you broaden your experience. I know people saving a LOT of time with a better architecture just because they do NOT use SP’s. Arguments on Frans’ blog.

    I find Rob’s points – with all the respect I have for this guy in the ASP.NET area – very wrong. If I would ever find some SQL specialist work with following some of his arguments (like the caching one) I would make sure he leaves the company immediatly, as he does not know his profduct.

    Dont get me wrong – I am not saying SP’s are useless, far from this. There is a lot of functionality that sometimes greatly benefits form SP’s.

    But sadl, 99% of the database operations are just not well served with SP’s, and they make a terrible architecture. Even with YUKON.

  15. I too was deeply entrenched into the world of stored procedures, with exactly these same arguments as my rationale for it being the correct way. I was shocked when I started to learn that these arguments were simply not actually true, and I too did not want to believe I had been misled. I finally started testing some of this for myself, using O/R mappers like Thomas’ EntityBroker and others, and reading some of the other side’s stuff too. Now I still use stored procs at work, because I’m not up to fighting this battle yet, but that will change when ObjectSpaces comes out with Whidbey! Down with stored procedures — long live SQL!

  16. Well said, Paul.

    This is poropably how SP’s are used in a LOT of cases, and then using a SP is nearly a crime – the price paid in terms of development time is significant. Extremely high.

  17. Wallym: I know you are using Oracle, for Oracle the world looks different: Oracle has a strong procedure environment, where prodecures are pre-compilable. (other databases like DB2 do that as well: the compile the proc to C and compile that to native code) and Oracle’s runtime optimizer isn’t that great compared to SqlServer’s. Rob’s post was however targeting SqlServer, and you should see it all in that perspective as well.

    A year back I would be all on your side Wallym 🙂 However I learned the hard way. Others should avoid that misery and learn the right way: by collecting information and take the right decision. I think both sides are discussed in full now, others brought up good points as well and I sometimes write a stored proc here and there (f.e. a sequence emulating sproc in sqlserver), but this is rare.

  18. >That’s 172,800 database connections and network IO usages that you no longer require!

    Two points: first, your amendements to the sample are still not convincing, IMHO. If you can do it in an SPROC, you can do it in a single multi-statement SQL command as well. So, using T-SQL inside the code does _not_ mean more connections per se!!

    Second, I think the question is not if there are any (specific, special) examples when it is wise/a good idea/necessary(?) to use a SPROC instead of a parameterized query. The question is if your general idea of using SPROCs always and everywhere is really a good one. And I still don’t think so 🙂

  19. What do those of you advocating using ad hoc SQL do in a situation where you are faced with needing to reuse an existing SQL query? Right now in the world of stored procedures, I may have one SP that gets called by three other SPs. If I ever need to change what that one SP that’s called by the others does, then I just need to go to one place.

    In the ad hoc world, it seems that there are two options.

    1) Make multiple calls to the database(not so good).

    2) Write the necessary query code in each of the three pieces of ad hoc code(very bad, I can easily see someone not knowing or forgetting about another block of code).

    How do you deal with security? Do those of you using this approach limit permissions for a logon on a per table/column basis? This seems to be overly tedious to me, and not entirely secure. With a SP I’m assigning authorization to a particular process.

    An example of this might be if you had an application where a client could bring back a list of their customers.

    Using SPs, I can implement a procedure that takes in @ClientID, and only returns customers for that one client, and then in turn only grant access to that stored procedure to users logged in with the Client logon.

    With the ad hoc approach, I would have to grant SELECT permissions on a table to the Client logon. Which means that if a query is written in which the @ClientID parameter is left out, that client will now get a dump of the entire customers table. Or what if customers are also allowed to login to the system? The Customer logon will now most likely need SELECT permissions to the customers table, since they would need to see their profile or some such. What if a bit of logic is implemented wrong, and the customer now all of a sudden can get a dump of the customers table?

    It seems like the above situation is much less likely to happen with a SP, as a developer would have to explicitly go in, and grant permission to the existing customers retrieval procedure, or would have to create a completely new one, and then grant those permissions.

    How do you handle moving your code to another platform? I realize most people here develope only with the .Net platform, but what happens when you need to take your code to another platform that does not support .Net, but you still need to access the database? You now have two codebases across which to maintain your queries, regardless of what you try to do. You can no longer modify you ad hoc query in your one data layer as you could do with one central stored procedure.

    What about UDFs? Since those advocating ad hoc are against SPs, are you also against UDFs? If so how do you get around using them?

    We frequently use UDFs in our SP statements, whether it be for determining column values for a query, or for being used as a table. I can honestly say that without them, life would be *much* more complicated.

    I suppose I come across as being quite SP-centric, but that’s probably because I am. I currently maintain a database with 900+ SPs, and about 300 UDFs, and am quite fond of both of them. But I am definitely interested to hear what advocates of the ad hoc approach have to say, because what has been said so far has piqued my interest, and I don’t have a problem with changing if there are truly ways around all of the above listed issues.

    Steve

  20. So Frans has reared his head in a charming manner. Hmmph.

    Anyway, here is a question..in real life how many programmers out there trot their code around multiple databases? Not that many I would bet. And as for the ones who do have this requirement – why write a multi-configurable dynamic SQL system when you could simply modify your SQLs? As for the guy who thinks that his programmers couldn’t or don’t want to learn a new database system’s SQL structures (like its all THAT hard) …how are do you expect them to write the Dynamic SQL statements for the new systems if they don’t want to learn anything?

    What it comes down to is that SPs have a lot going for them, some of it built in – like vastly increasing the difficulty to a hacker. Why chuck the advantages away when they are handed to you on a plate just because (after a lot of effort and (possibly unwarranted) faith in your own ability to see all the possibilities) you can? Simply there versus working for it.

    No-one says you can’t write dynamic SQL where you need a versatile database reading capability – it makes plenty of sense. But leave the ‘I can write the world’s best dynamic SQL generator that is beyond all hacker’s reach and all bug’s penetration, and can handle all database needs and I will simply because I can’ to the university campus.

  21. http://

    Quoted from Microsoft web site:

    "SQL Server processes every query in several steps. It parses the query, compiles the best (in its educated opinion) execution plan, and places the chosen plan in the procedure cache. Then it executes the query. Once the query is complete, SQL Server removes the plan from the cache.

    Stored procedures, views, and triggers are handled differently. The first time somebody invokes an object after SQL Server is restarted, a plan is created for this object and placed in the procedure cache. The plan for a stored procedure depends on the actual values of parameters passed to the stored procedure at the first execution. The plan doesn’t disappear from the cache after execution is complete. It may be used again and again by subsequent calls. Procedures created with the WITH RECOMPILE option are an exception, because their plans are recompiled and reoptimized at every execution. "

    Those who don’t understand what is meant when DBAs and others who know say a stored proc is compiled should do some reading. While there are methods to keep the plan for dynamic SQL in cache for longer than normal, it is not the same as what a stored proc does. As mentioned in the article security is a major factor too. It is very rare that direct access to any production table (in an OLTP aot a reporting DB) is preferable to access via a stored proc simply due to security and the possibility of injection attacks. Frankly paramaterized queries still suffer from poor security design. Ultimately without access only via stored procs I have access to the underlying tables and can do anything I want to them.

  22. http://

    I use stored procedures. Because we have a DB guy and and ASP guy I find that this speeds development. The DB guys writes and tests the SProcs. This makes debugging for the ASP guy easier, he doesn’t have to debug (or write for that matter) the SQL. Work can be done in parallel and in co-operation.

    Our development is with one database MS SQL, we aren’t a software house, just the companies internal IT department. So Mr Ed’s comment is quite true here.

    There are times when a simple select is all that is needed and that is done in the ASP. And we find using selects in ASP when retrieving from a text type field much easier than a SProc.

    I keep an open mind on these things and we use what we think is best in each particular situation. There must be a balance somewhere and is surely different for each company and each situation. Getting it right is the professionals call, hell if it’s always easy we’d be out of a job.

  23. http://

    This goes so interesting. Any conclusions.

    So for I feel StoreProc leading the debt.

  24. I use sprocs whenever needed or whenever the particular "data access SQL" will be hit a lot, otherwise I tend to use easier-to-maintain sql batches with named parameters (this provides an easy way to step up… just name the sproc parameters the same, if possible)

    Of note, Visual Studio Magazine makes some claims that parameterized batches are more efficient than sprocs, but I tend to think something was overlooked. It was the November 2003 issue, "Optimizing SQL" something or other

    So apparently the debate will rage on! 🙂

  25. I did a search for "talk about sport" as I am looking for information on where Halifax Mooseheads players lived and the history about them and their culture. We have and have had hockey players from all over the world play with the local hockey team here in halifax, Nova Scotia, Canada. I am researching anyone who has played in the past and the present for the Halifax Mooseheads hockey team. I came upon your website when I searched for sports, the words "talk about sport" must have been on this webpage , thus it put you in the search engine for the word "talk about sport". You seem to know alot about computer teckie stuff. I enjoy reading what you say, I must admit I havenot the slightest clue what you are talking about, but you sparked my interest and you got me sidetracked reading about it for the last couple hours. It was very interesting reading, although I didnot find anything about any Moosehead players. This website here is like the tv shows I like to watch of the so called sports experts sitting around a table ribbing each other with their opinions, but here you say your opinion about anything and everything.

    Anyways, good luck with your website.

    It maynot be what I was searching for this time, but I will be back to see how your opinion has changed or not.

    I like seeing peoples thru their opinions, sometimes you see a totally different person.

    Take care and God Bless

    Steve A Halifax Mooseheads Hockey Fan

  26. what as stupid little smuck you are Steven… Mooseheads Hockey?

  27. Peter: Why do you think stored procedures are hard to debug? Just keep your profiler in the background, and debugging is very easy.

  28. This is another case of developers shooting from the keyboard. As developers we are provided with tools and those tools may be useful one day and outdated the next. Why would someone avoid using Sprocs? Why would someone avoid dynamic sql statements? The key is to know the benefits and shortcomings of both, which has been emphasized here, and making a sound decision on which is appropriate. I recommend using sprocs for multiple updates, such as updating a time and inserting a record, or simple return statements, such as select FIELDS from TABLE where FIELDID = parameter. I would also recommend using dynamic sqlstatements for akward query building. When it comes to updating a database though, I think you should have one account with ONLY update permission. Use a sproc to retreive the recordset, make your updates and use ado to post the updates under the account with update permission. This describes a web application that does not use windows authentication of course.

    – Use sprocs for consistent statements and multiple updates.

    – Use dynamic sql statements for akward query

    – Use ADO for updating recordsets under an update only account. (The security to fetch the recordset has an enormous impact if you think through this model. How can you update a recordset if you can’t fetch it?)

    Most important? Realizing you have more than hammer and nails in your toolbox. If you use ADO Commands you should really invest the time into understanding them to the best of your ability. Don’t copy and paste examples from the web and code on the fly.

    Robert Shaver

    "Don’t be a cut and paste programmer"

  29. in real life how many programmers out there trot their code around multiple databases? – Not me.

  30. http://

    It is truly amazing to see the amount of different opinions here, some good, others coming from what seems like a purely dogmatic point of view.

    One thing to remember is that stored procedures are just a part of the whole and how easy they are and how much benefit they provide depends upon whole. The whole includes not just your design but also productivity, maintenance, performance and scalability. If they are "easy" or "hard" to use then what you are really saying is they do or don’t work with your existing process.

    For us stored procedures fit easy into our development process. Here are some of the benefits beyond what Rob mentioned:

    – All our sp’s are sql scripts that are added and modified via osql.

    – Now they can all be version controlled, with an exact history of who screwed what up *grin*.

    – Each script has it’s own unit test (JUnit\DUnit-style). Make a change and run the tests. Not "wait for the user to hit the site and have it crash". Also, ever tried to write a unit test for an ASP page? MUCH harder.

    – If we are tempted to run a simple little select then we are forced to reevaluate the page. Are we doing any other DB-related work on that page? Can it come back with another procedure? Almost always the answer is yes. I’ve got even our advanced dev’s with two ad-hoc SELECT’s because they didn’t realize another dev was already getting some piece of information elsewhere on a page with multiple includes.

    The benifits of maintence and debugging via unit tests FAR outweights the difference of a few minutes it takes to create a stored procedure.

  31. http://

    Francis Hanlon post is quite good I want to add or emphasis:

    Yes stored procedures are compiled which means query plan is made and stored. But that is not the only speed advantage of SPs. In addition they reduce network round trips and therefore significant delays. With SPs SQL Server 2000 can execute about 5000-10000 SPs per second. Please try this with sending plain SQL queries and you will always avoid sending plain SQL queries.

    Don’t forget to use parametrize queries. If you do your job right you will seldom need to use result sets at all.

    And against some posts before I say debugging T-SQL is much easier than debugging your code no matter what language you use.

    In addition even if you want to do encyrption tasks or so use E-SP and the performance will be incredible.

    Or to say it simple:

    1.) SPs are always faster

    2.) There are many arguments pro SP and no argument against.

    (Ignoring some special cases like programming a DBA tool, or things like that)

  32. Great topic! The biggest advantages of using stored procedures in my opinion are that of reducing network traffic and limiting the transaction time (or connection open time) of updates which has a huge impact on scalability. This is because unless you use Oracle (with it’s non-ansi compatible row-version locking) readers will block writers and writers will block readers during the transaction. And network roundtrip times will end up getting included within this transaction time unless you use stored procedures.

    When it comes to reducing network traffic, the same effect can be achieved if your queries are optimized well and properly parameterized, with the exception of iterative data-driven batch processes.

    When it comes to reducing the transaction cost , we hit another problem – it’s achievable only if you decide to do most of your transactional business logic in the stored procedure, which could become a maintainence nightmare (and discouraged in the article). So you have to decide whether to have all your business logic in the SP and getting locked into that db and db-language flavour OR in the middle tier – it’s not worth splitting the business logic in two places.

    So in conclusion you are left with the following:

    You have to decide between high scalability by including ALL your business logic in stored procedures or high maintainability/db independance. Given that HW is cheap and labour is expensive I always lean on the latter.

    For iterative high-volume data-intensive batch processes, Stored procedures are a viable alternative – and most often the maintainability compromise is worth the gain. You can also use statement batching to reduce the network roundtrips if you decide to stick to the middle tier.

  33. http://

    Actually paying attention to your algorithms will solve most of your performance problems. Using an n^2 algorithm instead of O(n) implementations, for instance, will have far more impact than anything you can do on the database/IO side. If the database is slow, you can cluster it or buy another box if necessary. Heck, DBA’s are cheap right now. if your algorithm sucks you’re screwed.

    My two cents.

  34. http://

    Hi,

    Im a newbie, i only started learning about SQL two weeks ago but my boss expects me to ride one hell of a steep learning curve….anybody can offer me some help as to how to build STORED PROCEDURES? Any easy to learn resource available on the web?

    I already know the basics like SELECT and INSERT….thats about it

    would appreciate any help anybody can offer

    cheers

  35. http://

    In one of my projects we had more than 2000 stored procedures. They had lot of dependencies, it was hard to change this procedures. In my current project, I try to use stored procures only for for operations which is easier to do in TSQL than in C#. Such approach improved development speed. In this project we have only 50 stored procedures now, and it is easy to do changes. And I use all advantages of SQL Server – dont need to support multiple databases. Security – middle tier solve this problems.

  36. http://

    One point I’m surprised I haven’t seen in here is the need to de-couple logic from data. Suppose you have a need to make drastic changes to your schema…the stored procedure approach is a formal “interface” – so long as you maintain it, the app doesn’t break. Update the stored procedure in exactly one place and everything keeps working. Compare that to the case where you have inline SQL all over the place. Seems to me that without stored procedures or some other de-coupling technique, you’re sunk.

  37. For an example of how to create a ASP.NET application that is stored procedure based and supports multiple databases platforms, take at look at the open-source application SplendidCRM.

    My basic rule is that data out of the database should be through a view, and data in should be performed through a procedure.

    As for supporting multiple database platforms, SplendidCRM supports SQL Server, Oracle, DB2 and MySQL. And when Sybase fixes a bug in their .NET provider, we will support them as well. SplendidCRM uses SQL Server as the base and run the SQL code through a converter to target the other database platforms.

    http://www.splendidcrm.com/

  38. Exciting website.

  39. Sorry in advance for the rant..

    Not sure why there is so much time wasted on this argument? To think that there is one right way to access data is simply naive. Sounds like to me that developers are just trying to over simplify data access so they only have to think in one dimension or worry about one methodology. Well the big news is one size does not fit all.

    With .NET and SQL 2000/2005 MS has provided developers many ways to access data to best fit their needs or make them productive. For example, if one needs to enumerate records in the DB, and return a result, using a Stored Procedure will be much faster than returning those records to a .NET app and having the .NET app enumerate through the records. If you do not believe that just try a test with 1+ million records. So the questions becomes where do you put the SQL (BLL or SP) that will be sent to the server to parse the 1 million records. I agree with “It depends”.

    One of the big advantages, of Stored Procedures, that I have not seen addressed in this discussion is compiler checking. For example, if I have my code in a SP, change the DB and rerun the creation of the SP, the SQL parser will check to make sure the SP correctly references any DB structures (e.g. tables or columns) that I may have removed or changed. I do not see how SQL written in a BLL/DAL could ever check that the DB had a change. IMHO, this is a big advantage because I am alerted when I create my SPs that I may have a potential problem with my SQL. I do not have to wait until run time to see this. Talk about hard to debug and unproductive. And as far as debugging goes I can end-to-end debug my applications from Presentation, through BLL?DAL through SP and all the way back to presentation. SPs are not hard to debug. That is simply a FUD campaign waged by those who have not been able to set it up.

    For me this comes down to the situation, just like the article said. For example, the ISV would probably be better off not writing SQL in SPs because they have to support different DBs. On the other hand an ASP might be better off writing code in SPs, for reasons of enhanced security and consistency.

    The productivity issue comes down to who you have. The developers who use SPs will be more productive with SPs, those who are more comfortable with a SQL in the BLL/DAL approach would be more productive with the SQL in the BLL/DAL approach. Enough said on productivity..

    By the way, as of SQL 2005, SPs can do encryption/decryption. As for string manipulation, one may be correct to create a CLR SP to do this but that does not mean all your SPs should be written as CLR SPs. And more importantly string manipulation should not be a crux issue, except for maybe in certain applications, for how you make design decisions for your entire app. Where you need string manipulation use a BLL/DAL or CLR SP, but elsewhere use the tools that better fit the specific operation.

    Good developers are like master craftsmen. They should be aware of the tools at their disposal and should employ them effectively to meet the challenge at hand. To say one method is better than another and to make broad sweeping generalizations, especially about development tools, is a disservice to developers and customers alike. Stop wasting so much energy on standing on one side or the other and embrace to entire toolset. There is good stuff to be had through both approaches.

  40. create procedure ParameterizedSPUsingWhereWithCase

    @CustomerIDEquals nchar(5)=”,

    @CompanyNameBeginsWith nvarchar(40)=”,

    @ContactNameEquals nvarchar(30)=”

    as

    select CustomerID,CompanyName,ContactName

    from dbo.Customers (nolock)

    where CustomerID=case when @CustomerIDEquals<>” then @CustomerIDEquals else CustomerID end

    and CompanyName like case when @CompanyNameBeginsWith<>” then @CompanyNameBeginsWith + ‘%’ else CompanyName end

    and ContactName=case when @ContactNameEquals<>” then @ContactNameEquals else ContactName end

    go

  41. Fly fishing equipment fly tying supplies and accessor.

  42. I have a strange but genuine question. I really am not getting the thumb rule as to how a stored proc has to be used. I agree that its used to embed the bussiness logic. But on a more deeper level, what should a stored procedure be used for? I am not talking about the advantages here. My question is more like

    Is it to do some bussiness operation? OR

    Is it used to return recordsets?

    To be more specific.. Say I have 20 stored procedures in my application. What is the common nature that these procedures must have? Should all of them be performing some kind of business operation Or should they all be returning recordsets and/or what else?

    Excuse me if the question is not clear :0)

  43. http://

    I would like to know the extent of business logic which should be put in database by means of sps/triggers.Our application uses them extensively.

    Also,portability issue aside, why it is that people in Java world don’t use stored procedures as much as we do in MS world ?

  44. http://

    This article is valid when you are doing a single client software. If you want to reuse this software and install it on other production platforms Database dependency is a very big limitation.