5 ms·

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

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.

gaius 15y ago
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.
- peregrine 15y ago
  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.
  - gaius 15y ago
    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?
    - peregrine 15y ago
      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
      - gaius 15y ago
        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 :-)
- gnaritas 15y ago
  > 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.

gaius 15y ago

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.

peregrine 15y ago
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.
- gaius 15y ago
  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?
  - peregrine 15y ago
    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
    - gaius 15y ago
      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 :-)
gnaritas 15y ago
> 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.