Comment by peregrine | Hacker News Reader

Comment by peregrine | Hacker News Reader

This article reads very much like an article for Oracle, and thats not even my biggest gripe with this article. I have written my fair share of SP's in my very short career and I will say this.

Pros: Stored Procedures tend to be fast if you know what you are doing. Stored Procedures are a good way to abstract away the "relational" differences between OO and SQL.

Cons: Stored Procedures are a good way to write a bunch of horrible to maintain code. Most developers spend their time writing front-middle end code and don't write SQL that well so they write horrible SPs. Even worse is someone who really understands SQL and writes almost the entire application in a SP and writes awful front-end code. If I had a dollar for every time I opened up a 2k+ line SP with nested queries, queries written as strings and 'eval'd or an awful number of joins I'd probably be arrested for suspicion of dealing drugs.

In almost every company there are a small group of people who call themselves "DBA's" and believe in writing unmaintainable code for speed and rally against those using ORM's or things that basically remove them from the equation .

Its crazy the amount of pain I've witnessed and felt because all of a sudden I realize I have no clue with a SP is doing or why its written a certain way. And having to sit down next to these DBA's so they can "explain"(mostly fix it because they have no time to explain) it to me.

My last job we finally got the OKAY to try out Entity Frame work(.net ORM) but the DBA literally rallied for weeks, showing how much faster SP's are. We finally came to an agreement where we would use entity with autogenerated SP's as a backend and fall back to SP's on slow calls. I'm still not sure why he was given a say but it is what it is and I don't work there anymore.

Unfortunately this is common, but as a "DBA" I can tell you right now: it's inconsistent for a developer to sneer "databases don't scale" and yet advocate the use of an ORM that generates bad, bad SQL! When speed matters, you have to get the compute and the data as close as possible. You can slurp it all into the application and work on it there, or you can just do the computation in the database, which is actually easier, and more efficient.

FWIW I work on a trading system that happily handles thousands of commits/sec, in Oracle, along with complex calculations of exposure and the like.

I agree there is a point when optimization is required. At the time we were building a new product that had rapidly changing requirements and few users. So you can see my frustration when days of man hours where spent on this database.

You say the DBA "showed" that SPs were faster. It's hard to argue against actual timings. Why shouldn't he have had his say?

Because it was also shown in several project's before and the current one that writing these custom SP's and using autogenerated SP's added huge amounts complexity and development time to our application.

Which again was still being built and had no need to be a high performance machine. In fact none of the products that the company served up needed to be high performance. These guys were smart and bored and built huge monolithic systems full of complicated approaches to simple problems.

Edit: Further more nobody wanted to work with this guy on the team. He was an "expert" on everything and ran every single conversation about anything, including areas he had no experience in, into the ground because nobody's opinions were valid because he clearly knew everything. /rant

Well, if you were a PL/SQL dev, you'd be making the same complaint about the complexity of J2EE. I've no experience of working on systems where performance wasn't an issue :-)

> When speed matters, you have to get the compute and the data as close as possible.

And when scaling matters, you often need to do the opposite. And scaling is often more important than speed.

Ermm, that's nonsense. You can move the compute into the database (stored procedures) or you can move the data to the compute (caching - and deal with cache invalidation yourself). There is no situation ever in which you would deliberately seek to make them as far apart as possible.

Not necessarily nonsense: figuratively, you can (and should in many cases!) be putting as much of the data as possible, as far away as possible from your code with divide and conquer to work on only a fraction of it.

But anyway, that's what indexes are for in RDBMS, so you still have a point, even figuratively :)

> You can move the compute into the database

Yes, often doesn't scale because the database is an extremely limited resource that generally doesn't scale horizontally.

> or you can move the data to the compute

Which is what I said, which allows you to scale computation horizontally.

> There is no situation ever

You just named one, however I see where you're misinterpreting me or I you; when I said do the opposite, I meant move the computing away from the database.

Reply on news.ycombinator.com