6 ms·

Actually, I do remember 10 years ago, that was when I got my first job. I remember that already then I was rather surprised to learn that what they said in the

by pfedor 15y ago

Actually, I do remember 10 years ago, that was when I got my first job. I remember that already then I was rather surprised to learn that what they said in the books about database applications was exactly backwards: the often repeated wisdom was that the database engines were fast and highly optimized, and you should do as much processing as possible on the database engine and as little as possible in your PHP script which was bound to be less efficient. The reality was that the database engine was always the bottleneck. If the PHP script was slow, you could replicate your Apache to as many machines as needed. If the MySQL was crapping out, you could buy a bigger machine or optimize your queries but generally that was a bigger pain.

silverbax88 15y ago
If your database engine is the bottleneck, you don't have an enterprise architecture. This will sound incredibly mean, but I built a very lucrative career coming in behind people who tried to fix everything in their apps and not the DB.
- btilly 15y ago
  If your database engine is the bottleneck, you don't have an enterprise architecture. What's the busiest website that you have experience with? Do you have any experience with scaling to, say, serve more than a million dynamic pages per hour to consumers? I ask because your opinion seems extremely uninformed when it comes to high performance websites. If you've designed your application right in a traditional CRUD application, everything except the database layer scales fairly easily. The game then is about finding ways to help that remaining bottleneck. Standard techniques include optimizing queries, caching data intelligently, directing queries to read-only slaves, and in extreme cases sharding data across databases. Several of these result in moving more logic into the app, not less.
  - jacques_chester 15y ago
    I think the fundamental mismatch is not between OO and relational, or the application layer and the relational layer, but between reads and writes. In the database world this conflict has been studied for decades under the headings of OLAP and OLTP. All you get taught in a university course or a beginner's book is OLTP-focused. Great if you're storing thousands of credit card transactions per second. Less useful if you need to read thousands of records per second. Lots of NoSQL looks like it's actually dimensional modelling minus some of the really clever bits.
    - chopsueyar 15y ago
      Well, with the ORMs the objects generate the relational stuff. I enjoy modeling my entities on paper as an ERD (from the entity relationship perspective) and then writing the object code. However, I have found it is sometimes necessary to denormalize my schema and store entities based on how they are presented, not necessarily the idealistic OO ORM'ed way. At the end of the day, the user doesn't care if your code is abstracted properly (who's definition?) or enough. It just needs to work speedily and correctly and be easily maintainable. I think alot of people know SQL, but may not understand how to model entities correctly, and if they can't model the entities correctly, what ever they denormalize is still coming from a 'less-good' model.
      - jacques_chester 15y ago
        Personally, when it comes to denormalisation, my preference is that it be done in a semi-structured way such as dimensional modelling. And that's exactly what I was talking about: a key purpose for DM is to make the database more accessible for fast bulk queries at the cost of duplicate data. Whereas normalisation drives out duplication in order to maximise transactional performance and space efficiency. In your case, you start with the OLTP/normalised model, then devolve to what is actually a kind of OLAP/denormalised model. Write-oriented vs read-oriented. Maybe the missing link for relational databases in a web world is actually some kind of real-time ETL?
- simonw 15y ago
  What's your definition of an "enterprise architecture"?
electrum 15y ago
10 years ago ... MySQL MySQL could barely do joins ten years ago. Today, it's still behind where Oracle was ten years ago. You can throw extremely complicated queries at Oracle and it generally optimizes them well. Don't think of MySQL as "fast and highly optimized". It's only fast for relatively simple queries. When I say complicated queries, I'm talking about 50+ line queries with many joins, subqueries, aggregations, etc. You can run these against tables with millions of rows and still get an answer in seconds, often regardless of indexes.
- jacques_chester 15y ago
  With one exception: joining over database links is terrible. I admit that it's a hard problem to solve, but still. Just yesterday I rewrote a view that used a database link to act as a materialized view (which makes me feel icky). It ran approximately 10,000 times faster. For a 100-row result that only joins 4 modest tables, there shouldn't be that kind of a difference IMO.

by pfedor 15y ago

silverbax88 15y ago

If your database engine is the bottleneck, you don't have an enterprise architecture. This will sound incredibly mean, but I built a very lucrative career coming in behind people who tried to fix everything in their apps and not the DB.

btilly 15y ago
If your database engine is the bottleneck, you don't have an enterprise architecture. What's the busiest website that you have experience with? Do you have any experience with scaling to, say, serve more than a million dynamic pages per hour to consumers? I ask because your opinion seems extremely uninformed when it comes to high performance websites. If you've designed your application right in a traditional CRUD application, everything except the database layer scales fairly easily. The game then is about finding ways to help that remaining bottleneck. Standard techniques include optimizing queries, caching data intelligently, directing queries to read-only slaves, and in extreme cases sharding data across databases. Several of these result in moving more logic into the app, not less.
- jacques_chester 15y ago
  I think the fundamental mismatch is not between OO and relational, or the application layer and the relational layer, but between reads and writes. In the database world this conflict has been studied for decades under the headings of OLAP and OLTP. All you get taught in a university course or a beginner's book is OLTP-focused. Great if you're storing thousands of credit card transactions per second. Less useful if you need to read thousands of records per second. Lots of NoSQL looks like it's actually dimensional modelling minus some of the really clever bits.
  - chopsueyar 15y ago
    Well, with the ORMs the objects generate the relational stuff. I enjoy modeling my entities on paper as an ERD (from the entity relationship perspective) and then writing the object code. However, I have found it is sometimes necessary to denormalize my schema and store entities based on how they are presented, not necessarily the idealistic OO ORM'ed way. At the end of the day, the user doesn't care if your code is abstracted properly (who's definition?) or enough. It just needs to work speedily and correctly and be easily maintainable. I think alot of people know SQL, but may not understand how to model entities correctly, and if they can't model the entities correctly, what ever they denormalize is still coming from a 'less-good' model.
    - jacques_chester 15y ago
      Personally, when it comes to denormalisation, my preference is that it be done in a semi-structured way such as dimensional modelling. And that's exactly what I was talking about: a key purpose for DM is to make the database more accessible for fast bulk queries at the cost of duplicate data. Whereas normalisation drives out duplication in order to maximise transactional performance and space efficiency. In your case, you start with the OLTP/normalised model, then devolve to what is actually a kind of OLAP/denormalised model. Write-oriented vs read-oriented. Maybe the missing link for relational databases in a web world is actually some kind of real-time ETL?
simonw 15y ago
What's your definition of an "enterprise architecture"?

electrum 15y ago

10 years ago ... MySQL MySQL could barely do joins ten years ago. Today, it's still behind where Oracle was ten years ago. You can throw extremely complicated queries at Oracle and it generally optimizes them well. Don't think of MySQL as "fast and highly optimized". It's only fast for relatively simple queries. When I say complicated queries, I'm talking about 50+ line queries with many joins, subqueries, aggregations, etc. You can run these against tables with millions of rows and still get an answer in seconds, often regardless of indexes.

jacques_chester 15y ago
With one exception: joining over database links is terrible. I admit that it's a hard problem to solve, but still. Just yesterday I rewrote a view that used a database link to act as a materialized view (which makes me feel icky). It ran approximately 10,000 times faster. For a 100-row result that only joins 4 modest tables, there shouldn't be that kind of a difference IMO.