Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

blog.acolyer.org

Post reply on HN
Print view View original

41–50 of 319 posts

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

#41

Post by ianamartin »

czechdeveloper wrote: post #16
mozumder wrote: post #3

Its always amazing to see web pages take 2-4 seconds of back-end processing. On a modern CPU, that's about 10 billion instructions. 10 billion instructions to send a few kilobytes of data. There's such a waste in back-end server design. If you're measuring response times are in seconds, and not in microseconds, you're doing something seriously wrong.

From my experience, most of my waiting time is IO, not CPU load. Even simple stuff, such as SELECT COUNT(*) to display total count in grid on big table can take seconds. It does not have to be complicated.

You have a problem. And it’s probably related to your db architecture, which was probably designed by devs with ORMs.

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

#42

Post by ianamartin »

rypskar wrote: post #35
czechdeveloper wrote: post #16

Earlier quoted context omitted.

From my experience, most of my waiting time is IO, not CPU load. Even simple stuff, such as SELECT COUNT(*) to display total count in grid on big table can take seconds. It does not have to be complicated.

>Even simple stuff, such as SELECT COUNT(*) edit seems like my old performance book was outdated or wrong :(

Nope. Not even close.

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

#43

Post by manicdee »

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’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.

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

#44

Post by reacweb »

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.

Writing better code makes programs more robust and reduces maintenance cost. For a management point of view, the boss may think that your project was more easy than the project given to the other team that butchered their work.

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

#45

Post by TekMol »

manicdee wrote: post #43
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’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.

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

#46

Post by blowski »

fuzzy2 wrote: post #37
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…

An ORM doesn’t have to be slow. Most can source their data from custom queries or stored procedures. Not using an ORM requires writing lots of code to do what the ORM would otherwise do. Is that really appropriate today? Should we not take advantage of the available CPU/RAM? I say ORM is the correct choice in many cases. Not always.

ORM doesn’t have to be slow, but I have seen a lot of implementations like the parent comment. For example, doing a foreach over 2000 rows where an UPDATE would be much, much quicker. ORMs help a lot with developing software, but you need to profile and optimise - in some cases bypassing the ORM.

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

#47

Post by dagw »

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.

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

#48

Post by olavk »

marcus_holmes wrote: post #36
olavk wrote: post #22

Earlier quoted context omitted.

Writing SQL by hand does not solve typical performance problems like n+1 queries. If on the other hand you know enough to avoid n+1 queries, then you can also avoid them when using an ORM, and save a lot of work. If you like writing SQL by hand, by all means do so, but you will not automatically get better performance by handwritten SQL as compared to ORM generated SQL.

No, it's not automatically more performant; it's more that you can optimise hand-written SQL for your specific schema and use case. You can't do that with ORM-written SQL.

Depends on what kind of optimizations, and on the ORM I guess. The major problems described in the article, like n+1 queries and filtering on the client rather than in the database seem to be easier to fix in an ORM than in hand-written SQL with boilerplate wrappers. (Of course they are also easier to introduce in an ORM if you don't know what you are doing, but such is the curse of powerful abstractions.)

Even things like query hints can be easily applied in the ORM's I know. It is kind of hacky since it breaks abstraction layers - but so is query hints in SQL.

But of course there can be some special cases where you just have to drop down to raw SQL for some reason. All ORM's I know allow this.

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

#49

Post by weavie »

idle_zealot wrote: post #38
patkai wrote: post #33

Earlier quoted context omitted.

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.

So long as you're using prepared statements, you should be okay.

What difference does using prepared statements make?

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

#50

Post by blowski »

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.

Looks similar to Laravel’s Eloquent, which is inspired by Rails.
Post reply on HN