How not to structure database-backed web apps: performance bugs in the wild • Hacker News Board

Re: How not to structure database-backed web apps: performance bugs in the wild

#161

Post by pc86 » Thu, Jun 28, 2018, 12:28 PM UTC

TekMol wrote: post #21

Give me an O! Give me an R! Give me an M! What does that spell? SLOW PERFORMANCE! Todays programmers dont understand data. They understand frameworks. To find the nr of all cars that are out of insurance they write: 10 Nr=0 20 Hey framework, give me all cars! Framework: Ok, here are 8001093 business objects representing all the cars in our DB. Each has all the attributes the car has. Color, mileage etc. 30 Thanks! 40…

This isn't what happens when you use an ORM, this is what happens when you have a JavaScript code monkey writing data access code and then slapping the word "Enterprise" on it.

The places I've worked at that have written true Enterprise software, whether consulting, publicly traded healthcare companies, banks etc, have had extremely knowledgeable DBAs writing stored procedures for data access. The ORM usage was reserved for modifying the results of stored procedures or small things like account management. Nothing truly critical to the business was handled through an ORM directly pulling data from tables.

Re: How not to structure database-backed web apps: performance bugs in the wild

#162

Post by candu » Thu, Jun 28, 2018, 12:30 PM UTC

dagw wrote: post #47

TekMol wrote: post #21

Give me an O! Give me an R! Give me an M! What does that spell? SLOW PERFORMANCE! Todays programmers dont understand data. They understand frameworks. To find the nr of all cars that are out of insurance they write: 10 Nr=0 20 Hey framework, give me all cars! Framework: Ok, here are 8001093 business objects representing all the cars in our DB. Each has all the attributes the car has. Color, mileage etc. 30 Thanks! 40…

That has nothing to do with ORMs. I've seen plenty of people write code like: SELECT * FROM DATA for each r in result if r.x > 12 do_something(r.y) Exactly the same thing without any ORM. If anything ORMs should improve performance for novices since it makes it a lot easier for people to writer better queries that run on the database.

I've also seen:

  SELECT * FROM DATA WHERE x > 12

where there's no corresponding index. There's also the infamous N+1 query pattern:

  // get list of ids
  for each id in ids
    r = SELECT * FROM DATA WHERE id=:id
    if r.x > 12
      do_something(r.y)

IMHO, both are signs of not fully understanding what the database does for you. In the same vein as the "learn JS before frameworks" argument, I'd argue devs should at least learn and understand SQL, indices, etc. before using ORMs.

Re: How not to structure database-backed web apps: performance bugs in the wild

#163

Post by pc86 » Thu, Jun 28, 2018, 12:32 PM UTC

coldtea wrote: post #112

manigandham wrote: post #85

Earlier quoted context omitted.

Not this again. ORMs are tools, basically dynamic code generators that run SQL and map the results to in-memory objects, and vice-versa. Some are simplistic and others are incredibly advanced, and the code itself is usually faster than your own sql->objects logic that you would write otherwise. The issues with performance are almost always with the way the tool is used, like choosing a bad algorithms or the wrong dat…

> The issues with performance are almost always with the way the tool is used That's overly generic. The truth is that some tools encourage bad performance habits and a lax attitude about it whereas others don't. ORMs do.

I think the benefit of using an ORM outweighs the cost of making sure you don't have bad programmers on staff. We should be utilizing code reviews and proper source management anyway, right? This should be as simple as a senior/lead developer seeing someone iterating over 8MM rows and saying "don't do that."

Re: How not to structure database-backed web apps: performance bugs in the wild

#164

Post by acdha » Thu, Jun 28, 2018, 12:33 PM UTC

dagw wrote: post #47

TekMol wrote: post #21

Give me an O! Give me an R! Give me an M! What does that spell? SLOW PERFORMANCE! Todays programmers dont understand data. They understand frameworks. To find the nr of all cars that are out of insurance they write: 10 Nr=0 20 Hey framework, give me all cars! Framework: Ok, here are 8001093 business objects representing all the cars in our DB. Each has all the attributes the car has. Color, mileage etc. 30 Thanks! 40…

That has nothing to do with ORMs. I've seen plenty of people write code like: SELECT * FROM DATA for each r in result if r.x > 12 do_something(r.y) Exactly the same thing without any ORM. If anything ORMs should improve performance for novices since it makes it a lot easier for people to writer better queries that run on the database.

My record was a 5 order of magnitude performance improvement on a site which a big consulting firm had been working on for most of a year, where page rendering times were somewhere north of 20 minutes (the server’s hard timeout).

None of their developers knew about WHERE constraints but they did know how to join tables so they were looping over hundreds of millions of rows in classic ASP using essentially the code you have above, with a bunch of unnecessary type conversions cargo culted into the inner loop (IIRC string to int to string).

Added business lesson: we billed double for a rush job but since it was only about 20 hours to fix the code and build out the remaining features (about half of the site), the original contractor still made considerably more. Our sales guy wasn’t canny enough to realize that he should have offered to do it for, say, a third of the other company's price.

Re: How not to structure database-backed web apps: performance bugs in the wild

#165

Post by sudhirj » Thu, Jun 28, 2018, 12:47 PM UTC

adrianmsmith wrote: post #125

sudhirj wrote: post #86

Earlier quoted context omitted.

Given that these are the people who wrote the Rails ORM, you'd expect that they know how to use the ORM to generate high quality SQL. Which is actually quite doable - in Rails / ActiveRecord you're much better served by knowing what happens for every ORM call, and the default development log prints every generated SQL query as well. Think it now also provides alerts when the queries are slow.

True, but my point was that that blog post supported the point that ORMs are sometimes promoted as a way to avoid needing to know SQL. The intended audience of that blog post was not people who write ORMs, it was to persuade people who are writing applications that they don’t need to learn how to use a database, that they only need to learn how to use an ORM.

Yeah, that's isn't going to work. Would want to use an ORM the way you'd use a bicycle - it won't necessarily let you do something you couldn't before, but it makes it easier. Using an ORM without knowing the SQL it generates is like learning to bicycle without learning to walk. You'll fall down at some point and then you're well and truly screwed.

Re: How not to structure database-backed web apps: performance bugs in the wild

#166

Post by funkydata » Thu, Jun 28, 2018, 12:48 PM UTC

TekMol wrote: post #45

manicdee wrote: post #43

Earlier quoted context omitted.

That’s how I have seen it done in Perl, PHP, Django, etc as well. It’s not an ORM thing it is a naive programmer thing. The ORM makes it easier to do things like Class.filter(insurance_end_date But hey what do I know, I am not berating the youf of today so I don’t belong in this skit.

Class.filter(insurance_end_date Which ORM is that? It would be cool to compare some approaches to common problems.

The Django ORM for example.

You can ever traverse relations and still use the same syntax: https://docs.djangoproject.com/en/2.0/topics/db/queries/#loo...

Re: How not to structure database-backed web apps: performance bugs in the wild

#167

Post by manigandham » Thu, Jun 28, 2018, 12:54 PM UTC

coldtea wrote: post #112

manigandham wrote: post #85

Earlier quoted context omitted.

Not this again. ORMs are tools, basically dynamic code generators that run SQL and map the results to in-memory objects, and vice-versa. Some are simplistic and others are incredibly advanced, and the code itself is usually faster than your own sql->objects logic that you would write otherwise. The issues with performance are almost always with the way the tool is used, like choosing a bad algorithms or the wrong dat…

> The issues with performance are almost always with the way the tool is used That's overly generic. The truth is that some tools encourage bad performance habits and a lax attitude about it whereas others don't. ORMs do.

Encouragement and attitude doesn't force you to do anything. You still have to choose to use it, either well or poorly.

This is no different than any other tool that makes things easy but with obvious limits. Proper decision making is still up to you. There really isn't much controversial here if you get past the whole "ORM" hype/hate cycle.

Re: How not to structure database-backed web apps: performance bugs in the wild

#168

Post by littlestymaar » Thu, Jun 28, 2018, 12:55 PM UTC

TekMol wrote: post #21

Give me an O! Give me an R! Give me an M! What does that spell? SLOW PERFORMANCE! Todays programmers dont understand data. They understand frameworks. To find the nr of all cars that are out of insurance they write: 10 Nr=0 20 Hey framework, give me all cars! Framework: Ok, here are 8001093 business objects representing all the cars in our DB. Each has all the attributes the car has. Color, mileage etc. 30 Thanks! 40…

“Todays programmers”: I'm sorry to disappoint you, but this isn't new. My father (DB engineer, now on the verge of retirement) has been complaining about that for almost two decades now!

Just as a reminder, Hibernate is 17 years old :).

Also, modern ORM frameworks give ways to iterate on the data from the programmers language, but translated directly to SQL. See what [Diesel](http://diesel.rs/) does, for instance.

The paper talks about other issues, which comes from negligence or a lack of understanding of the performance costs of the queries. But this performance impact could aslo happen in a SQL-only environment (not using a framework won't help you to create indexes, or to add pagination to your queries).

Re: How not to structure database-backed web apps: performance bugs in the wild

#169

Post by paulie_a » Thu, Jun 28, 2018, 1:06 PM UTC

asfdsfggtfd wrote: post #105

threeseed wrote: post #61

Earlier quoted context omitted.

> ORM makes it much harder to see what is going on under the hood I've never in the last 20+ years seen an ORM that doesn't allow you to log the SQL queries with a single configuration option.

With an add on Django even displays them in the browser!

It's trivial to find optimizations in django debug toolbar..."oh yeah I should use select_related"

If you have no idea what you are doing with an orm you probably won't be able to write decent SQL anyways.

Re: How not to structure database-backed web apps: performance bugs in the wild

#170

Post by FLUX-YOU » Thu, Jun 28, 2018, 1:07 PM UTC

patkai wrote: post #33

TekMol wrote: post #21

Give me an O! Give me an R! Give me an M! What does that spell? SLOW PERFORMANCE! Todays programmers dont understand data. They understand frameworks. To find the nr of all cars that are out of insurance they write: 10 Nr=0 20 Hey framework, give me all cars! Framework: Ok, here are 8001093 business objects representing all the cars in our DB. Each has all the attributes the car has. Color, mileage etc. 30 Thanks! 40…

I love to write SQL queries and even use them extensively in my code. However, almost everyone I talk to resists this, and warns me that "one day you will regret..." It gives me an uneasy feeling that I actually might, though it hasn't happened yet.

The only way you'll regret it is if you type the query directly into an editor which doesn't support syntax highlighting or doesn't validate the query before you run it and it blows up in production. This is 'typo crashed my code' kind of stuff.

The solution becomes really obvious if it happens once for 95% of people good at computers.

You might regret it a bit more if you do something like a filesystem read to get the query.

There's your regret. It hurts so much, doesn't it? :)