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

171–180 of 319 posts

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

#171

Post by professor_plum »

FooBarWidget wrote: post #17
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 find this question to be very weird. I have never seen ORMs as tools for completely abstracting away the database. I have always seen them as convenience APIs for using the database. They exist to make your code shorter, less repetitive, more readable, easier to reason about, and more maintainable; not to make you forget about the database altogether. I also don't see ORMs as exclusive. It's fine to use ORMs for 95…

They also let you compose SQL fragments in ways that would be a lot more difficult or less clear otherwise. In the case of Diesel, they let you typecheck the composition of those fragments. ORM hate is based on misunderstanding of the benefits those who do use them get from them.

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

#172

Post by hungerstrike »

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.

SQL is way more expressive than most ORMs, so solving problems is easier in general.

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

#173

Post by thermodynthrway »

It's a bit high level to mention this, but it doesn't have the one query problem I see constantly. Paging using offset and limit. I swear every app I've worked on uses it somewhere. And it's horrifically inefficient 90% of the time.

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

#174

Post by YorickPeterse »

qaq wrote: post #146
guipsp wrote: post #116

Earlier quoted context omitted.

This is not the case for pretty much any modern ORM

Sure it is show me the ORM that will support 2p commit, window functions, CTEs, JSON operators in PG (just a random set of features)

http://sequel.jeremyevans.net/

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

#175

Post by vorpalhex »

thermodynthrway wrote: post #173

It's a bit high level to mention this, but it doesn't have the one query problem I see constantly. Paging using offset and limit. I swear every app I've worked on uses it somewhere. And it's horrifically inefficient 90% of the time.

Can you elaborate on the issues with that? I've used that pattern and haven't found any major woes (yet), but also don't usually paginate complicated queries.

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

#176

Post by dagw »

acdha wrote: post #164
dagw wrote: post #47

Earlier quoted context omitted.

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 yo…

Hey, I think those same consultants worked on the main website for a company I used to work for :)

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

#177

Post by stetrain »

thermodynthrway wrote: post #173

It's a bit high level to mention this, but it doesn't have the one query problem I see constantly. Paging using offset and limit. I swear every app I've worked on uses it somewhere. And it's horrifically inefficient 90% of the time.

Probably still better than putting all the rows in a temp table with row numbers and then selecting where row number is between x and y, which I have also seen.

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

#178

Post by matwood »

mullsork wrote: post #154
AsyncAwait wrote: post #122

Earlier quoted context omitted.

This is mostly due to its popularity and various people needing different use cases, the long-time maintainer is now working on a mach cleaner ORM for Rust, called Diesel[1]. 1 - http://diesel.rs

Compile time SQL query checks... amazing! I've been trying to find a similar library for a while. Thanks for linking

jOOQ for Java does something similar. It's so good, I'm likely stuck with Java on the backend until I stop using RDBMs.

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

#179

Post by matwood »

candu wrote: post #162
dagw wrote: post #47

Earlier quoted context omitted.

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 u…

> I'd argue devs should at least learn and understand SQL, indices, etc.

This was all dev 101 when I was coming up (yikes, almost 20 years ago now).

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

#180

Post by chasd00 »

superasn wrote: post #28

This may be a little OT (OT because the points raised in the study are totally valid and mine is just a comment) but for small companies and solo developers ORM or whatever that gets the job done quickly is the way to go. Most sites and web apps never even break a 100k/day hit mark for which I believe inefficiency may not be the biggest issue. But wasting a month tryig to write native Sql queries can hurt your projec…

i always take the approach of get it functionally complete first then optimize for performance. It's easier to track optimization bugs (which can be nasty) and you always have something to revert to that at least works.

I agree that 99% of all projects never push beyond what relational databases are capable of. I've seen developers who were tasked with writing basically a todo list say that they're going with a nosql database because RDBMs don't scale.

Post reply on HN