Fog Creek Software
Discussion Board




Database Stored Procedures - Good, Bad, Ugly?

I have done mostly old-style ASP programming. One application has many hundred web templates and many hundred stored procedures (SP). The problem I have noticed is that when researching say a bug, I have to look at both the template and the SP to hunt down the problem. Perhaps it is a problem with the logic in the template, or perhaps the SP is complex and needs some tweaking. The point is, the logic is divided amongst the web and the DB, so it is a pain in the rear to debug, maintain, etc.

So, now I am working with the new ASP.NET programming style. I love that you can put all your business logic in DLL's and do your thing outside of the page. Of course, MS stills seems to be pushing the SP's and I know a lot of people I work with are married to the idea of using SP's.

From what I understand, the execution benefits of SP's is no longer that great as SQL2K does a spiffy job of caching SQL execution plans. What are your feelings on SP's? When should they be used? Do they make maintenance more complex? How successful is it at hiding you from schema changes?

m
Tuesday, September 02, 2003

Heh.
I just spent two days optimizing three SP's that were originally written with some faulty assumptions. The SP's are called from about ten different locations each.

So - if I'd used embedded SQL, I would've been fixing thirty sets of byzantine concatenated SQL statements.

Put it this way:
Stored Procedures are directly analogous to DLL's/Business objects. I cannot fathom any logical construct that endorses OOP or even procedural programming but eschews SP's. Well, just one - "I don't like Stored Procedures because they're too hard."

[and don't forget - SP's are your first line of defense against SQL injection attacks!]

Philo

Philo
Tuesday, September 02, 2003

For me, one of the largest reasons for having our database code wrapped up in SP is that we can quickly and easily tweak the SP without having to recompile the code.

Another reason is security. Depending on your RDBMS, you can tweak security by changing a user's permissions to the tables, stored procedures, etc.

And it just "feels right" to have my database code wrapped up in a SP. It makes me feel dirty when I have a bunch of SELECT statements embedded in my code. Granted, this is somewhat subjective, but I guess it just goes with some degree of experience.

Mark Hoffman
Tuesday, September 02, 2003

I often create a view layer ontop of the tables to reduce complexity of the SQL code needed in the code. I much prefer to have SELECT * FROM ORDERS_V in my code and this ORDERS_V be a view displaying only a subset of the ORDERS table depending on the user querying the DB.

When the view creation logic gets hairy and contains all obscure constructs and UNIONs and sub selects and stuff, I usually create an SP to produce the needed results, to simplify the creation of the view. So the view might look like this:

CREATE VIEW ORDERS_V AS
SELECT O.* FROM ORDERS O
WHERE O.ID_DEPT=GetUserDept(USER)
AND O.SOMEFIELD=SomeBusinessRuleApplies(USER)

...or somesuch. Drawback of this technique is that every user needs their own database user, so the view layer can distinguish who is running the queries.

I find it worthwhile though to add as much business logic as close to the database as possible.

Feel free to disagree :-)

Patrik
Tuesday, September 02, 2003

I have nothing against stored procedures per se, it's just what they end up promoting, which is a migration of *all* of the business logic into SQL, no matter what the cost. I lost several family members in an SQL migration storm in February 2001. The terrible memories.

SQL contains too little of the constructs of a good programming language and I find code quickly becomes unreadable in the quest for performance, bearing the responsibility of code that should not be there. I would hope that down the long road that SQL is refined into something more... co-operative with the development game.

I've spent many a day trying to understand whole pages of meta-data driven business logic which has been "optimized" and somehow shoe-horned into SQL form. And then my old nemesis, the Dreaded Cursor appears, who bears an uncanny resemblance to the Hooded Claw.

My 2 coinage: good, provided they're not overused.

Joel Goodwin
Tuesday, September 02, 2003

Oh and by the way, did I mention that - right now - I am overusing stored procedures?

god damn that's *hypocrisy* boy

Joel Goodwin
Tuesday, September 02, 2003

Joel,

I understand where you are coming from; I have done
these "all business logic in SPs"-systems on Oracle DBs.

Oracle has PL/SQL which is richer than most other SP languages I have used/seen; with the ability to do File I/O, TCP/IP callouts, HTTP callouts, XML parser, an event driven client alerting system and what have you.

This is all standard.

You get alot with the package that helps simplify porting the business logic to the database.

Patrik
Tuesday, September 02, 2003

PS. The overusage of any substance - be it drugs or stored procedures, constitutes abuse, and thats always bad for your health :-)

Patrik
Tuesday, September 02, 2003

I had heard that Oracle was quite a different world altogether and I been thinking to look into it some more, as all my experience is from the world of Sybase and MS-SQL.

(Now I'll be thinking to look into it some more some more.)

Joel Goodwin
Tuesday, September 02, 2003

We do all so called business logic in database (Oracle PLSQL) and use PHP layer only for presentation. SP-s are created from perspective of GUI - ie if we have combobox on screen then there is one ref-cursor returning the contents of combo and so one. Pretty much on SP call per one web page. And I find it works very well.

There is very little confusion about where error's come and programmers track them down pretty quickly.

We typically use teams of two (for one particular subsystem) - one database programmer, one web programmer - both goot at their own area and this arrangement produces quite good results.

We also make use of code generation and generate PHP procedures for each SP so PHP programmer has no need to even construct [PL]SQL procedure call.

Sometimes I have to maintain our old IIS-ASP-MSSQL code where there is SQL inside VBScript inside HTML... and besides it looks pretty ugly it is also very hard to debug (copy SQL out, strip down apostrophes and so one).

Additionally - Oracle wont let you compile SP if you have missing tables-columns in your SQL which makes it easy to detect object changes - you can do this in script embedded SQL besides extensive testing.

So - go for SP-s but make sure the interface (proc headers) are well understood by both programmer (best result is when they nail down proc headers during desing).

msc
Tuesday, September 02, 2003

Sorry for typos - cannot see them in edit box :-(

msc
Tuesday, September 02, 2003

"I just spent two days optimizing three SP's that were originally written with some faulty assumptions. The SP's are called from about ten different locations each. So - if I'd used embedded SQL, I would've been fixing thirty sets of byzantine concatenated SQL statements."

Only if you were a bad developer.

Stored procs aren't the only way to isolate data access from data consumers.

Brad Wilson (dotnetguy.techieswithcats.com)
Tuesday, September 02, 2003

Just stumbled over an AskTom thread SP vs J2EE EJB-s.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:12083187196917

Nobody you know
Tuesday, September 02, 2003

"Stored procs aren't the only way to isolate data access from data consumers. "

Then I must be missing something. What possible reason is there to say that


DB | ------- | Data Logic | ------- | BL | ----------- |Presentation

Is "better" than

DB (Data Logic) | ------------| BL | ----------etc

?

And I'm still kinda fuzzy about why putting database code outside the database helps performance. ;-)

Philo

Philo
Tuesday, September 02, 2003

One arguement may be that its MUCH easier to scale thinsg across multiple application servers.  Its pretty standard to have multiple app servers with only one database (maybe a second one that just mirrors one, but not actually distributed).  If your putting logic that isn't directly relevant to SQL in stored procs, you are hurting performance.  Now, i'm not advocating you do in java or C# what you could have done with a Select statement, but other non-query things are a bad-practice. (such as text parsing, iterating through results, stuff like that).  Also, don't forget, in a stored proc, you loose the abililty to dynamicly generate queries, and while thats generally a bad idea....sometimes you look back and wish you could do it. :-)

Vince
Tuesday, September 02, 2003

Stored procedures don't cost you the ability to dynamically generate queries.  Though there are some limitations.  I posted this not too long ago, but it's worth a second reference.

http://www.algonet.se/~sommar/dyn-search.html

Matt Conrad
Tuesday, September 02, 2003

Cool article.  I know you *can* dynamicly generate SQL in a stored procedure...but doesn't that kind of defeat the entire point of having a stored procedure? 

Vince
Tuesday, September 02, 2003

Rod Johnson discusses the J2EE vs. stored procedures argument in his book "J2EE Design and Development", I'll loosely (and liberally) quote his points as his argument is well written.

Objections to stored procedures include:

* Stored procedures tie you to a DBMS. This goes against the Java mantra of portability, but in practice it is much more likey that the implementation language (eg. COBOL, C, C++, Java, .NET, whatever) will change while the DBMS platform is fixed. Data in the DBMS is too valuable to risk swapping platforms.

* Stored procedures may become complex and stored procedure syntax is typically not as expressive as Java. If you ensure that stored procedures implement "persistence" logic (not "business" logic) this is less likey to happen.


Benefits of stored procedures include:

* Can handle updates spanning multiple tables. Hard to achieve via O/R mapping.

* Stored procedure performance will often be very good.

* Stored procedures are easy to call from Java leading to simpler implementation (than embedded SQL, entity beans, etc).


He goes on to discuss the infamous Java Pet Store vs. .NET Pet Store debacle (Java solution used EJBs, .NET solution used stored procedures - .NET was benchmarked faster) and concludes: "The benchmark did not prove that J2EE is inherently less performant than .NET. The architectural approach Microsoft used could easily be implemented in J2EE (more easily than Sun's original Pet Shop example, in fact), but it did prove that J2EE orthodoxy can be dangerous."*

* From memory the .NET solution also stored HTML in the database which is an example of an inappropriate optimisation.


Before digesting Rod's comments I was probably of the opinion that stored procedure were a bad idea because of the portability issues. After reading his discussion about stored procedures and application server portability I'm convinced of his argument - target your solution to the platform you are working with while being mindful of the potential for portability (e.g. you can provide generic functionality while providing an optimised implementation geared towards your deployment platform). Also, having a knowledgeable DBA is a big plus - as the Ask Tom link proves.

Walter Rumsby
Tuesday, September 02, 2003

"I'm still kinda fuzzy about why putting database code outside the database helps performance. ;-)"

Putting data access code outside the database doesn't help performance. In some cases it hurts it, and in some cases it's a wash. There are other reasons to keep your data access code out of the database:

- Easier to change DBs or support multiple DBs

- Your HLL is likely more expressive than SQL

- Single point of maintenance

- If you're mapping relationships into object graphs, you're already into a significant amount of data access code

The reasons for using stored procs are pretty well documented, and not worth repeating; after all, they are the party line these days. :)

It comes down to a question of the intended usage and application architecture. We don't use stored procedures; using them would be, at best, a micro-optimization for us (since we're using MSSQL 2000).

Brad Wilson (dotnetguy.techieswithcats.com)
Tuesday, September 02, 2003

m,

Stored procedures are usually (in my experience) used for two reasons:

1) Speed
2) Security

With my experience with MS SQL Server, I have never been convinced by 1). The overhead of executing dynamic SQL over stored procs is (in most circumstances) neglible.

2) is more valid, though many places I have worked at are using a single login for all database access (through MTS / COM+) so that argument doesn't work in those cases.

The major problem I have with stored procs is that:

a) They are often used when VIEWs or FUNCTIONs would have been a better option. VIEWs or FUNCTIONs have the security benefits, plus can be queried against.
b) Related to a), one of the big problems with stored procs is that are fixed with a certain ORDER (you could support multiple ORDER BYs, but only using if statements etc). Dynamic SQL handles this more simply (you could sort the data on the client as well, but sorting is one thing that database servers do VERY well).

Seeya

Matthew
Tuesday, September 02, 2003

" Your HLL is likely more expressive than SQL"

Still reads to me like "I don't like TSQL 'cause it's different and hard"
I find my comments to be equally readable in either platform...

Philo

Philo
Tuesday, September 02, 2003

I don't find TSQL difficult at all. I'm very adept at it, as writing a data layer still means writing SQL. What you don't get to do in a stored proc, though, is mix your HLL with your SQL (at least until Yukon).

As for your ill-found perception, that's your problem, not mine. :)

Brad Wilson (dotnetguy.techieswithcats.com)
Tuesday, September 02, 2003

"As for your ill-found perception, that's your problem, not mine. :)"

Hey now - when I decide to be obstinate and judgmental as a career move, that is EVERYONE's problem. Understand?

Philo

Philo
Tuesday, September 02, 2003

heh, good point. :)

Brad Wilson (dotnetguy.techieswithcats.com)
Tuesday, September 02, 2003

Ah now, this Brad/Philo thing is the core of my concern with SQL.

My situation is that I'm developing an interface for an entity on the database and DLLs on user desktops are currently out of the question (another story I won't go into) so stored procs were the best option for implementing this.

I have many beefs (a whole herd) with this. I'll pick on one. The only way to exchange sets of data between the stored procedures was to use temp tables. It's the only real way to achieve encapsulation but there's negligible scope/access control (a db channel isn't scope control in my book). I'm working on MSSQL and table variables are a nice idea but they can't be used as calling parameters for stored procs. By definition I suppose, it's procedural and I want to code in a far more object-oriented fashion.

This is an example of what I consider overuse. SQL doesn't handle this sort of stuff really well, it's better suited for a higher-level language as Brad asserts.

Joel Goodwin
Wednesday, September 03, 2003

Joel - I agree completely that SP's, like anything, can be overused. There is a tool for every task; part of why we're paid so well (and apparently Brad is overpaid) is recognizing when to use which tool without discounting any tool for no good reason.

Philo

PS - [g,d,r]

Philo
Wednesday, September 03, 2003

"What you don't get to do in a stored proc, though, is mix your HLL with your SQL (at least until Yukon)."

For information, Oracle 9i supports Java stored procedures.

John Topley (www.johntopley.com)
Wednesday, September 03, 2003

What's a "HLL"?

Duncan Smart
Wednesday, September 03, 2003

High Level Language?

Patrik
Wednesday, September 03, 2003

I've seen a number of posts that give database independence as a reason for not using stored procedures.

I'm curious, how many of you have been able to build a large application that targets either SQL Server or Oracle and re-use your SQL statements between the two platforms?

In my experience, once you get beyond simple SELECT statements, the different dialacts of SQL between RDBMS dashes any hopes of simply re-using the same SQL statements for different database platforms.

We've wound up having to tweak the SQL statements to tweak performance, etc and we end up with SQL that isn't portable. (Not to mention things like dealing with date and time data types is not usually portable either.)

Personally, I've found that trying to maintain platform independence in my database code just doesn't work. I prefer to write to the database that I am targeting while trying to avoid any really esoteric tricks that would be a nightmare to port.

Mark Hoffman
Wednesday, September 03, 2003

I have to second Mark.

IMO database independency is more or less myth and could be implemented only in small-scale applications.

Any application which has at least modest performance and scalability requirements has to be optimized - and optimization is database specific.

And - why should I pay $$$ to Larry if I will not use options available in Oracle? Hierarchical queries; PL/SQL for SP-s; autonomus transactions; SP libraries like SMPT, TCP, XML, encryption, pipes; background jobs or queues; etc.

For an example - with Oracle I can generate invoices in PDF (using iText) and e-mail as attachments (using Javamail) unattended at night - and not leaving database. Why should I install and maintain separate server environment for this? I have paid for Oracle already and I'll better max out for that money.

Nobody you know
Wednesday, September 03, 2003

I'm a newbie to the forum, please be gentle. With respect to database independence: I've been involved in the development of very large scale commercial apps that needed to run on heterogeneous databases, and we were able to achieve that pretty well with the JDBC API, which resolves the vast majority of the differences in them.

It is true that you end up writing to a lowest common denominator of SQL, but that turns out to be a powerful set of functionality. As has been mentioned earlier in this thread, you can get good performance through the use of the prepared statement, which capitalizes on the cached query plans used by most of the big DBMSs.

In most cases optimization is more a question of DDL and effective index design than tweaking DBMS specific SQL knobs. However, it is true that for the relatively few truly awful queries we ended up with special versions for each DBMS. The good news is that these represented far less than 1% of the total queries in the app.

Ken
Wednesday, September 03, 2003

The other thing on database independence: If you are building an app to sell, it is really, really nice to be able to run on whatever DBMS the customer is already running.

Ken
Wednesday, September 03, 2003

Ken,

Nice yes. But not critical. If your application does something non-trivial; be it a financial trading system, clinical information system or something of that nature people ususally dont care which database your application runs on.

The business benefit of such an application saves more cost or generates more revenue than the cost of the infrastructure anyways.

This is my experience.

Patrik
Thursday, September 04, 2003

*  Recent Topics

*  Fog Creek Home