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

31–40 of 319 posts

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

#31

Post by marcus_holmes »

olavk wrote: post #20
crocal wrote: post #4

... Which begs the question: what good is an ORM if it does not prevent by design such issues? Here, we are essentially saying users of ORM must also have in their mind the SQL version. Or call an expert after the mess is done :/...

The value proposition of ORM's is they simplify data access code, making the application simpler to write and more maintainable. I have never seen anybody claim ORMs would eliminate performance issues.

Right up until the point where you have to hack around the ORM to get performance where it needs to be. Then you suddenly have more complex and harder to maintain "magic" code than if you'd just written some simple-but-boring boilerplate and SQL from the start.

I'm wading through the tedious and boring process of writing a data access layer for an application at the moment. It's repetitive, there's lots of error-checking, and testing it is a pain in the arse. My big consolation, though, is that in a year's time when I need to optimise the queries because we're getting load on it, it'll be easy to understand and simple to change.

ORMs don't make code easier to understand, they make it less boring to write. Those are not the same things.

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

#32

Post by arkh »

willvarfar wrote: post #11
crocal wrote: post #4

... Which begs the question: what good is an ORM if it does not prevent by design such issues? Here, we are essentially saying users of ORM must also have in their mind the SQL version. Or call an expert after the mess is done :/...

I'm not very sure that Jooq and SQLAlchemy and other 'not quite ORMs' really help that much either. I think, as the article suggests, that 'opaque' is as much the problem as the classic Object-Relational impedance mismatch, but I would posit that the opaqueness isn't just that the programmer can't _see_ what happens, but that they also don't _care_. So I'd put my faith in tools instead. If programmers don't want to t…

> You could take this further for normal non-DB code too - they could warn me when I have inefficient O(n) search in a loop and so on.

For phpstorm you have the EA extended plugins which gives lot of hints like that. I'm sure you could find the same kind of plugins or write one backed by a static analysis tool for other languages.

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

#33

Post by patkai »

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.

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

#34

Post by ianamartin »

sfvisser wrote: post #27
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…

People do this because it’s easier, takes less time, and is within the paradigm their head is already in all day. We should fix our abstractions instead of telling people they don’t understand data. The latter is fine every so often, but doesn’t scale and isn’t even true most of the time.

Or maybe we should just nut up and tell people they don’t understand data?

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

#35

Post by rypskar »

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.

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

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

#36

Post by marcus_holmes »

olavk wrote: post #22
siscia wrote: post #10

I never understood why I don't enjoy working with ORM but reading this article make it clearer for me. They blend the distinction between working in memory vs accessing the db. From one side it is very convenient, however I really feel that such performance sensitive operation should be carefully considered and that most SQL should be written by hand.

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.

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

#37

Post by fuzzy2 »

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.

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

#38

Post by idle_zealot »

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.

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

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

#39

Post by siscia »

olavk wrote: post #22
siscia wrote: post #10

I never understood why I don't enjoy working with ORM but reading this article make it clearer for me. They blend the distinction between working in memory vs accessing the db. From one side it is very convenient, however I really feel that such performance sensitive operation should be carefully considered and that most SQL should be written by hand.

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.

Sorry, I believe I wasn't clear.

Definitely writing plain SQL does not solve performance issues. However, it does force the developer to draw a clear line distinguishing where he is accessing the database and where he is working with in memory data structures.

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

#40

Post by olavk »

marcus_holmes wrote: post #31
olavk wrote: post #20

Earlier quoted context omitted.

The value proposition of ORM's is they simplify data access code, making the application simpler to write and more maintainable. I have never seen anybody claim ORMs would eliminate performance issues.

Right up until the point where you have to hack around the ORM to get performance where it needs to be. Then you suddenly have more complex and harder to maintain "magic" code than if you'd just written some simple-but-boring boilerplate and SQL from the start. I'm wading through the tedious and boring process of writing a data access layer for an application at the moment. It's repetitive, there's lots of error-chec…

I guess this depends on the framework in use, but for example, in Hibernate you add a single "include" clause to avoid n+1 queries. It is only "magic" if you don't understand how it works, but then your SQL would be just as inefficient.

Any ORM I know allows you to easily drop down to raw SQL when you need it - but in the majority of cases you never need to. Writing everything manually in SQL with tedious boilerplate wrappers because you might need to manually optimize some queries at some point in the future seems like a massive violation of the YAGNI principle.

Post reply on HN