Who Needs Stored Procedures, Anyways?

blog
81

My issue with inline sql is that some developers tend to gravitate to it so that they don’t have to rely on a DBA or, they simply don’t trust a DBA to get the job done or to care about the job that needs to be done.

The company I work for is an Oracle shop. We program in C# against an Oracle back-end. As with any Oracle shop, we have a rather large DBA group who focus their efforts each day on creating and maintaining PL/SQL Packages and Procedures. Their constant tuning and maintenance assures us that our applications are running as good as they can. However, some of our developers do not like to utilize this talent and instead like to completely mimic Oracle Procs within C# code using inline SQL.

My problem with this is that it is a dual effort. Upon release, the DBAs will continue fine tuning and modifying the existing procs that are still used ad-hoc and when schema changes are necessary they will make all the changes to their procs but our C# code will be left behind and thus we will have to play catchup.

I was really surprised when I started here to find that the main developer here was basically taking the “ideas” behind the currently used and maintained procs and then rewriting those within his C# and inline SQL. The outcome of his effort would be similar but not the same as the effort the DBAs did.

When asked why this was being done the answer was “I hate Oracle” and “inline procs ensure portability”. I ask “portability from what”. Oracle is very expensive and I really don’t see the company moving from Oracle to SQL Server any time soon so how does this factor in at all.

This has been irritating enough but one thing that really bothers me is that many of the pro-inline SQL guys here tend to break their SQL apart so that it cleanly displays on the coding screen and this is all well and good for printing but when you search the code looking for what code affects what Tables or Columns, it is very difficult because the Table and/or Column names could easily be broken apart within a string to go to a new line such as:

SELECT * FROM Customer_
Data WHERE Last_Contact
_Date = …

In the above case, the Table Name is “Customer_Data” but you will never find that in a search, and the Column being referenced is “Last_Contact_Date” but again, you will never find that in a search.

Another thing that irritates me is that inline SQL tends to lead developers to forget about development tiers… they will just toss inline SQL anywhere. For example, in a web site I maintain, all the inline SQL is in a resource file that is part of the web site… fine, its organized. But, the business tier has inline SQL listed as the values of Constants. Of course, I use the term “business” tier loosely because the web site does a lot of database interaction where it just handles it all on its own… clearly, N-tier development was forgotten here.

That leads me to the conclusion that while inline SQL may have its place, it does tend to lead to lazy, messy code. People tend to forget about layers or tiers of code and think in the moment… this needs to be done and I’m here so there is no better place.

Personally, I prefer all base data interaction to be through procs on the database server, I can live with minimal inline SQL but breaking down incredibly time consuming data processing that is working well in a native proc and creating a slew of inline SQL statements to replicate the proc is only going to lead to sloppy code, confusion, performance decreases and security risks.

Oh yeah, to make matters worse, the web app doesn’t do any validation to protect itself from SQL Injection and couple that with inline SQL and you have a nasty mix.

Just my $0.02

82

and if BadAsh thinks its bad today, wait until sometime soon when SQL is replaced with “much newness and coolness” of LINQ. Then MS devs will be recreating all the failed EJB-type apps in C#. Its yet another great excuse to avoid tried and tested mature best practices in favour of new toys.

85

Jeff, you contradict yourself quite a bit in this blog, but I enjoyed reading it! I follow that you ultimately believe stored procs are a waste of time, or not necessarily if the case is right.

My belief is that if you have the talent to develop procs along with app. code then it is the only way to fly. I have been writing scalable enterprise software for 14 years and have use all major dbs, ORA, MSFT, etc… along with C#, ColdFusion, Java, etc.

Middle or client tier code becomes very legible with proc calls rather than in-line sql and per your pros I think they far outweigh your cons. There are only a couple of languages out there now that truly handle in-line sql coding well. Ever try to write sql statement in C#??? ACCCK! Each line with quotes and underscores, painful.

I am dealing with a project now where a developer doesn’t understand the performance ramifications of looping over sql calls at the middle tier upwards of 1000 iterations to retrieve data reflecting parent/child relation in his object creation.

Lack of sql knowledge plagues the industry and this lack of knowledge always makes the client or middle tier solution look bad when there is a performance issue. I rarely see performance issues outside of db integration (i.e. indexes missing and the scenario I described prior) It needs to be understood that knowledge of procedures is paramount to writing scalable applications.

86

I find this attitude all over the place, and it’s a bit worrying. It’s symptomatic of that greatest of developer weaknesses, the misguided idea that they can do everything. I am a developer with excellent T-SQL skills (if I do say so myself), but I know that my domain is the application, not the database. I write stored procs and then pass them to a DBA to be checked and then added to the database. It also indicates another great developer failing - the idea that the database is simply there to serve you and whatever application you are currently building. Not so. I haven’t worked on one project where the database is there simply to serve a single application - there’s usually two or more applications, probably being written by different people, that take data from that DB. Having data access tied solely to stored procs has three large benefits:

  1. Security. The applications/users do not need to be given direct access to the database itself. They are simply given rights to the stored procedures they need to use.
  2. Reduce redundancy. If you have 3 applications connecting to your database, they share some, but not all, of the same calls, and you are using dynamic/embedded sql, you can either create one sql library to hold all db calls, or create 3 seperate ones that share some of the same code. This means you’re either shipping code that will never be used, or duplicating the code that is shared. (Proper OO wisdom would actually see you create a shared library, then 3 application specific libraries - quite a lot of work).
  3. Maintainability. Surely easier to simply change an sp than dig through your code to find the bit of sql that requires altering, especially since, in sql server 2005, sysdepends will tell you what procedures reference which tables.

Also, if you’re using sps, you can make use of nested sps and sql functions to build a dataset from other datasets, using proper, sql-based set functionality. But this is another failing of the developer attempting to interact with the database - they don’t really understand how SQL works. In the article above, the author states that ‘JavaScript is a giant step up from PL/SQL or T-SQL’. Not true - sql is simply a language with a very specific purpose, but how many times have I seen developers treating it like any normal procedural language? Sql code can be quite elegant, but most developers choose tortured if…elses, and that most heinous of sql crimes, the cursor. Why? Because it’s most similar to how they’d use their own language.
The author should also be ashamed that he states: ‘Stored Procedures hide business logic’ as a criticism. This is a good thing - it’s called encapsulation. Your database documentation should tell you what sort of data you can expect to get back from a stored proc. You should never have to look at source code to be able to see how to interact with an API, whether it’s a stored proc or a class library - it’s bad PROGRAMMING practice, not just bad sql.
The author also states that none of the supposed benefits are true in practice. Sounds to be me like he’s never worked in an environment where good database practice has been established. Of course stored procs allow for a better security model - as long as you’re actually thinking about security. And yes, they provide a central point of control for data-access, which surely eases maintainability across multiple applications - but if you haven’t designed for maintainability, chances are you’re not thinking that far ahead.
In short, I’d argue that the author’s prime reason for not using stored procedures isn’t on some grounds of practice, it’s really just that he can’t be bothered. Another great crime of the developer.

87

Another point that I should have made above - if you use stored procedures, users can run them directly from the database in situations where you just want the raw data. This means that your users do not require sql skills, and nor do they table-level permissions - they simply need permissions to the procedure.

88

Here is the thing it is the only reason I think SP is better.

Lazy half ass programmers tend to copy and paste sql all over the place and do stupid things like that. So when I have to change a SQL block I have to track down and change 5000 locations. Opening another IDE, maybe the one I keep open to test SQL in seems a lot less troublesome…

So SQL is some strange language you can’t understand… wow you must write really good code.

Let me see here… oracle sucks use Sql Server because you can set break points in stored procedures in vs.net with sql server. OMFG

So this sounds like an I hate oracle more than SP sucks

And if your to lazy to do thing well I hope you stop programming because when I have to come work on your half assed buggy code with the same sql in 50 places I’m going to get pissed

89

My God you people can go for years about this topic.

Its all tools. Evaluate and use what is best that is my suggestion. I have seen dynamic SQL being used in realtime systems that handles more than 325 000 concurrent connections(connection pooling).Log files that grew 500 GB a day. 7 Million users. Over 210 Million database transactions per day. However the stored procedure versions was easier to maintain and performed just as well.

Its like a tool. You dont use a hammer to get a screw in You can do it but it does not make it a good idea. It is what is going to be best to use here that matter. Having inflexible views and ideas is idiotic. Its like saying mine is bigger than yours.

Anyway my 2 cents worth.

90

i believe that stored procedure is tops it just sound to me that there are a few coders out there that is looking for a short cut to do stuff.if u are not a coder then we will understand why u dont like sql sp’s then first get the hang of it.Code first before u comment on sql sp

91

I agree with Chris on all the points he has made and YouSoundLazy. It seems that the argument is not necessarily about the use of in line queries or SPs, its basically a question of attitude to the quality of the product they are developing. The first is from the amateur half hour crowd who cannot be bothered to learn how to produce clearly layered, secure, maintainable software, those that think cutting and pasting chunks of code is acceptable. These individuals are, to put it bluntly are the scourge of the software development world. The fact these individuals are typified by the fact that they think having inline SQL queries dotted all around there code is not due to the fact they have think they have real techological advantage, it is the fact they can’t be bothered to do the job properly.
The other side are the individuals that think that laying code properly, encapsulating busniness logic, making their applications secure at all levels, and are not selfish enough to think they are the only individuals who may have to pick up the code and run with it. These developers make huge efforts to make the code maintainable, flexible and future proof. They would avoid the use of in-line queries like the plague (if they are able to) because the strategy is, to there way of thinking, just plain wrong. In otherwords these individuals are professional, responsible developers who have a pride in their work and think about the fact that although it takes longer to develop in the initial stages, effort pays dividends when the application needs to be debugged, upgraded or modified, in the coming months and years.
Although sometimes it is not possible to use SPs depending on the DB being used, it is still possible to ensure that your code is structured to allow ease of maintenance by having a predefined DAL and encapsulating business logic.

92

Anyways is a word, in both America and across the pond. AnywayS here’s the proof
= a href="http://dictionary.reference.com/browse/anyways"http://dictionary.reference.com/browse/anyways/a

93

Just came across this thread, I feel strongly about this so I’ll add my vote to the “SPs are your friend” crowd.

I have cleaned up numerous applications that had inline SQL all over the shop. Lazy developers, contractors who come and go, several iterations of the development cycle will do that. A maintenance nightmare, since you invariably come across multiple variations of the same SQL, all with their own flavor. Put it in an SP, call it from the DAL, done.

The portability argument isn’t practical in the real world - I’d argue that the vast majority of apps. are developed for a single platform and live on it for the lifetime of the app. But - I recently ported a large Oracle app. to SQL Server (customer tired of paying the premium for Oracle). Thankfully, all data access was in Oracle SPs - MS provides a tool that converted 80-90% of them.

94

i can’t believe this is even a debate. proper use of sp’s will optimize the use of the database and simplify a code base. it’s that simple. as mentioned before, each database is optimized differently and if you think your app code will run quicker than optimized sp code, you’re wrong. i urge any developer working on an app based in oracle to pick up Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions by Tom Kyte. you will instantly become the oracle guru on your app team.

95

mate - you’re f’ing kidding eh? If your stored procs aren’t portable that’s cos you ain’t got the genius to implement generics… never mind fella - you just write inline like the first grader you are…

96

Ran across this and just had to comment too- on the Stored Proc side- especially Oracle. You are nuts to make multiple calls across a network with sql to a database. First- if you are using literals you will kill your performance. The number 1 problem. How many developers run explain plans on their sql? Second- Oracle can pass objects around, take in mulitple parameters, and spit back a dataset- 1 trip across the network. (provided you are not retrieving millions of rows). Try to trouble shoot when multiple apps may hit the same database/table- who knows what code is running. Not everthing should be a stored package/procedure or a function- but it should be used where it makes sense- lots of processing of lots of data. And I have seen some absolutely crazy processing that I can’t even imagine being done elsewhere- efficiently.

97

I noticed this old article when looking something up on google. Perhaps a follow up post is needed (have you changed your mind considering the majority of comments are against you).

Putting my 2 cents in I prefer stored procedures especially for business logic which lets face it is mostly where the complex sql comes in.

One reason few seem to mention is the fact I can have multiple interfaces in multiple languages running the same stored procedures. This makes it easier to change an interface to an underlying sql query.

This really helps when you move from a windows app to a web based implementation, all you have to consider is the interface you don’t have to go looking around in the old program for what the sql is and copy it meaning if you are maintaining the old app you now have two sql sources to maintain.

My final comment is in my job (work at a college as Management Info Specialist) we have queries that may change each year for the student and course information due to changes in funding and data collections… Stored procedures allow me to send in the year and it performs the correct sql so no streams of if statements in my app code they are hidden in the stored proc where I will know the exact reason for them. I am sure many others have queries that change year on year.

98

“When all you have is a hammer, everything starts looking like a nail”

In my experience, stored procedures are a tool in the toolbox, nothing more, nothing less. The same thing applies to JavaScript, c#, classes, inheritance, interfaces, delegates, user-defined functions, dynamic SQL, polymorphism, XML, YAML, REST, HTML, VBbscript, TDD, refactoring, inline SQL, Mock Objects, rapid prototyping, reflection (need I go on?).

The problem is most developers code solutions based on familiarity, not the right tool for the job. When the going gets tough, many developers just say I know (insert aforementioned tool), therefore the best way to solve this problem is with (insert same aforementioned tool). They forget to stop and ask is there a better way to do this.

99

My god, all the mindless anti-SQL FUD being regurgitated! Get over sprocs – they’re not composable, and you waste network resources the way they’re typically written.

Someone recently implied that dynamic SQL meant literals. Well, that’s patently false: the proper dynamic SQL would use parameters even for constants, to increase the usefulness of the query plan (to be honest, this is something a smart server would deal with in parsing, but I digress…).

Many implied that dynamic SQL meant spaghetti code. Also BS: proper dynamic SQL still resides in well-considered places, be they embedded in entities or in a completely seperate DAL.

Security: hogwash – lock your tables all you want, but provide views, TVFs, and UDFs. Don’t kill your app by resorting to non-composable SPROCs. Ad-hoc doesn’t mean chaos, nor does it mean giving up even easy security.

You know what? LINQ to SQL supports all of this, and nothing stops any other LINQ to RDBMS provider from doing the same: literals are parameterized, SQL produced is often better than what humans produce (when the provider can prove that the transform is safe – optimizations that rely on knowledge that doesn’t exist in the mapping won’t be made), the SQL produced can make use of server-specific features without having to re-write the code…

Absolutely place processing near the data. But sprocs aren’t for queries; they’re for triggering actions, like well-defined, stable, repeated number crunching loops.

Hide-bound luddites refusing to seriously consider where the actual problems lie (piss-poor code reviews allowing spaghetti to develop) waste my time.

100

It’s amazing how many people here still think it’s either SPs or inline queries in every app. Thats what a service layer is for. The service layer speaks sql (or talks to something else that does) and returns nice objects to the business layer, doing validation, security, etc. on the way through.

I’m sick of hearing the security argument too. Apart from a few specific uses (reporting etc, where SPS are probably a good idea) how often do you have different users connecting to the database? If there are multiple projects using a service layer then the service layer account is the only connection. If it’s a simple site the the asp.net account is the only one connecting.

Has anyone stopped to consider how an ORM or sql generator would increase performance? Consider something like paging, it would be a pain to have to write this for every possible permutation of a query and theres probably a dozen ways to do it in most DBs.

Suppose we just discovered a faster way to do a paged query with an ORM there is one place to update and all queries from then on will execute the fastest way possible. With SPs you would have to update many thousands of queries, or not.

IMO the absolute best way to increase performance is code reuse (usually through OOP). One optimised function potentially increases the performance in thousands of places.

The rules I follow (can’t remember where I first heard this):

  1. Never use SPs.

  2. Use SPs when theres a very compelling reason to disregard rule 1.

  3. Occasionally review exceptions to rule 1. A better way to handle it may have developed.

101

LINQ o Hibernate for the best.
No SP calls
No sql embedded.

Dynamic SQL code generated for you on the fly by your HighLevel Programming language.
Use it on top of well designed Views.
SP for the Update/Delete/Insert statements, either called manually or integrated in the LINQ or Hibernate Paradigm (which eventually supports SP calls for such statements)

1 Like
102

I worked at a Pfizer Central Research in 1998 when
VIA GRA (the space was needed to post…sheesh!) was ratified and I was the WORKFLOW ADMIN and in charge of supporting the Medical Document Imaging System for the Case Report Form Library and I did everything in SQL on INFORMIX using DB-ACCESS.

Just be sure that ALL drives are MIRRORED on PRODUCTION DATABASES, and the servers are powered by a tested UPS.