RSS Feed

The Joel on Software Discussion Group (CLOSED)

I just read the article "The Project Aardvark Spec" - lots of great information as usual.  One interesting thing that I ran across was that you "hope never to use stored procedures."  Why would you not want to use stored procedures?

I've come across two different schools of thought when it comes to data access.

"Keep all logic in the code, the database should just store data."

The the advantage here is that your choice of database becomes less significant.  If you want to change databases you just swap out the database, reload the schema, and you should be set (I realize this is a simplified explanation).

"Use stored procedures, they have more advantages than disadvantages."

Yes, you tie yourself to a database and stored proc langauge (T-SQL, PL/SQL, etc), and there is now some business logic in the database.  But, stored procedures are usually faster than passing SQL statements to the database because they are cached in memory on the database server.  It also takes a lot of load off the application server since much of the database processing is done on the database server.  Stored procedures also are immune to SQL injection attacks, and they don't require your application developers to know much about they database.  They just call a stored procedure and manipulate the data it gives them.


The rule of thumb I've always gone by is that the bigger and more data intensive the application, the better it is to use stored procedures.

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

"The the advantage here is that your choice of database becomes less significant."

By the same token the reverse is also true. By placing your buisness logic in the database you can then use any front end (or mulitple front ends) to call the code.

It's harder to business logic into stored procedures than it is to write it into code.

I can understand the argument for putting business logic in code, but wouldnt stored procs even have an advantage for data access - inserts, updates, selects? 

I guess it is a trade off of time to market, and database flexibility over performance - which comes down to a business decision.

Thanks for all the feedback!

There may also be some security benefits to using stored procedures (i.e., by limiting the queries that can be run against a db to a set of stored procedures, you can reduce the risk of injection attacks).

"It's harder to business logic into stored procedures than it is to write it into code."

How so, when both are written in code, both using programming languages?

On another note, it is hardly possible to "reload the schema" into a different database vendor.  In addition to the obvious syntax differences, when different databases implement fundamental things, such as locking and concurrency issues, then a change in database requires a change in data access code.

I agree though that this is a commonly cited "advantage", although it is really not an advantage at all.

People worry unnecessarily about "being tied to one database". They rarely think about being tied to one OS, though. I agree with Tom Kyte ((Oracle guru) that it makes little sense to buy an expensive DBMS product and then not use most of its features. There are many apps where stored procedures make perfect sense.

I don't think it matters much. Changing something as big as an OS or database is going to produce bugs.

We inherited a large LAMP-driven website. We took the exact same code and put in onto IIS (with PHP for IIS) and trasferred the data from their MySQL to ours (also on Windows). Without touching the code there were NUMEROUS bugs to be found simply because Windows/IIS and Linux/Apache handle some things differently.

<--People worry unnecessarily about "being tied to one database". They rarely think about being tied to one OS, though. I agree with Tom Kyte ((Oracle guru) that it makes little sense to buy an expensive DBMS product and then not use most of its features. There are many apps where stored procedures make perfect sense.-->

This is a good point.  I think the most important thing here is to keep in mind that there are degrees of being tied to a database, and that you way you design your application can dictate your level of dependence.  This came up for me about a year ago when I was doing a large XML pull from a database.  Oracle has the functionality to return an XML result set, and not surprisingly, it does it in an oraclly way.  By using this feature, I lost the ability to immediately switch the app to another db (even ones that have a similar method).  To me, that's OK, and I didn't end up writing a set of methods to transform an ordinary resultset to XML (the client wanted oracle and paied for it, so why should they pay for extra development to avoid oracle's features).  But I did keep the XML generation portion of the code carefully abstracted away from the rest of the business logic so that I (or the next dev) could easily extend the class to do this programatically later.  To me, that's what agility is all about - instead of programming features that are currently unnecessary, you keep the design flexible so that you can adapt to change.  It really comes down to good programming - nothing wrong with external dependencies per se, but if you weave vendor specific code into your app, you're asking for serious trouble.

I think whatever happens you're really tied to your database anyway. Import filters between databases are notoriously crappy and no database I know of out of the main players has a feature that you just can't do without.

Deciding to use stored procedures saved my backend more than once on the last project. If one report had wrong data, it was easier to debug and fix the problem, without making any changes to the client.

+++I agree with Tom Kyte ((Oracle guru) that it makes little sense to buy an expensive DBMS product and then not use most of its features.+++

Sounds like "you should use it just because you can" irrational bullshit to me.

Based on the general principle of measured optimization, why not eschew stored procedures in principle (since they're harder to work with), but put them in during the optimization phase if they can demonstrably ease bottlenecks?

Right, Egor.  Because I should let a bunch of jumped-up code monkeys hack up something in Java or C++ to replicate functionality the DB and it's native stored proc languages do better.

Besides, what's better OO design than letting the business logic be encapsulated with the data?

(I hope no-one who is aguing for complete database independence here programs in platform-specific languages and APIs like C++ for the Win API or VB.  Never know when you'll wnat to change your operating system!)

+++Besides, what's better OO design than letting the business logic be encapsulated with the data?+++

Not discussing the highly questionable applicability of OO principles to relational databases, there's still the issue of not being able to put all (even much) business logic into SP's, so you end with it being scattered between RDBMS and your high-level language. Doesn't sound like a good design to me, except when lagnuage is the same, which is rare.

Egor

I suggest you read just the first 2 chapters of Tom Kytes Expert One-To-One book.

"(since they're harder to work with)"

I think that highlights one of the biggest problems, it's not that stored procedures are good or bad but many developers haven't got a clue how to code them.

In a vast majority of cases they are easier and more logical than writing in another language.

I don't have a problem with people not using them, what I have a problem with is people dismissing them simply on the basis they haven't really got an idea of what they are or how they should be written.

It blur's the edges somewhat but you can write your stored procedures in Java within Oracle. So any argument that you can't write your Buisness Logic in them is null and void.

I doubt that idea anyway, perhaps somebody could give us an example of buinsess logic you can't write in a stored procedure.

Here's another angle on it -- who is more likely to produce a performant and scalable database application: a PL/SQL or T-SQL developer, or a Java developer?

Databases are specialised tools, and require specialised people to code on them. Developers skilled in Java, C# or whatever probably (note: probably, not always) do not have the time, the skills, or the inclination to be great database developers.