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

271–280 of 319 posts

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

#271

Post by golergka »

wwweston wrote: post #265
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 actually make this calculation for most projects that I work on -- what level of traffic do I expect this to see? If it's a back-office function or is limited to a few hundred clients at a time, I don't even worry about performance. But that said... writing native SQL queries wastes a month? Um, no. ORMs are convenient, but writing select, insert, update, and delete queries by hand isn't that hard. It's mildly verb…

Writing anything is fast. It's maintaining it that becomes a problem.

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

#272

Post by Yokohiii »

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.

Don't let the dark side blind you. Managing data is complex. ORMs add more complexity and they make data look easy. IMHO the biggest deal with most ORMs is that the satisfy the programming platform and coders aesthetics.

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

#273

Post by kraftman »

bpicolo wrote: post #187
vorpalhex wrote: post #175

Earlier quoted context omitted.

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.

In postgres and mysql, at least, the database has to re-scan data every time you run a limit/offset query query. It gets progressively slower as your offset increases. The efficient way to handle it is to set a lower limit on the pkid (or other atomically increasing row) of the last record fetched, and fetch in ascending order e.g. SELECT * from my_table where id > (last_row_id_seen) ORDER BY id asc limit 20; Then yo…

What if you need to order by something other than id?

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

#274

Post by adamzochowski »

vorpalhex wrote: post #175
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.

Guys behinds PostgreSQL / CitusDB wrote awesome article on which methods of pagination work how well and where they fail.

https://www.citusdata.com/blog/2016/03/30/five-ways-to-pagin...

Highly recommended read.

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

#275

Post by blattimwind »

dang wrote: post #267
blattimwind wrote: post #199

Earlier quoted context omitted.

> Inefficiency exists even at small scales with ORMs. In other words, you have no idea what you're talking about.

Personal attacks will get you banned here, regardless of how wrong someone else is. You've unfortunately been uncivil in at least one other comment in this thread too. Could you please (re-)read https://news.ycombinator.com/newsguidelines.html and not use this site that way? The idea here is to post civilly and substantively, or not at all.

Ok.

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

#276

Post by tasuki »

acutesoftware wrote: post #138

I need a very good reason before using any external library in an attempt to keep the total code base as clean as possible. It is just too easy to be rushed and bring in a heap of code, so I prefer to use SQL instead of ORM's. All access to the database is done in a single module and they are wrapped in functions like below def get_table_as_list(user_id, cols, tbl, where_clause, params_as_list, conn_str, order_by="1"…

What exactly is the problem you're attempting to solve with this?

Bonus questions:

What about maintenance or admin queries which aren't tied to a specific user_id?

What about sql injection?

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

#277

Post by Terr_ »

ht85 wrote: post #269
Terr_ wrote: post #264

Earlier quoted context omitted.

I imagine all of the non-toy ones would support joins for getting raw tabular data or for restricting what single kind of item (e.g. "Customer") you're pulling out. Where it might get trickier is when you have something like a Customer-Region-Vendor join, and you actually want it to go ahead and create objects for the all of the Customers and Regions and Vendors whose data was pulled out of that one select.

It is actually not that bad if instead of a single query, you do one query per table. It even offsets some of the burden to the app, which is generally easier to scale than the database. The idea is that you query the "root" table, loop over the results and build an array of IDs, then do additional queries to the other tables with a "where whatever_id IN (...)".

My understanding is that a lot of SQL drivers support compressing the data anyway, so that the server (and client) avoid duplicating the data over and over just to fill the rectangular result.

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

#278

Post by NightlyDev »

olavk wrote: post #244
mnm1 wrote: post #231

Earlier quoted context omitted.

Lazy loading often fetches too little and eager loading too much. Most orms select all columns in all tables regardless of need. The query translation and hydration overhead of transforming the data into objects even in a fast language are always going to be problems, it's just a matter of how big, over a system without the orm. Since orms are superfluous, every line of code in them adds unnecessary overhead. They ar…

> The query translation and hydration overhead of transforming the data into objects even in a fast language are always going to be problems No they are not. Have you measured this in a real-world application? This overhead is negligible compared to the cost of the query itself. And without an ORM you still have to load the data into some kind of objects or data structures before you pass it to presentation, you will…

It's definetly not always going to be a problem, but sometimes it does. ORM is always slower than writing custom code, but it might be more than fast enough.

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

#279

Post by WorldMaker »

vidarh wrote: post #214
theapemachine wrote: post #130

Earlier quoted context omitted.

Pretty sure that is what tailing the log during development is all about. I don't see what is so obscure about it, in the Rails based examples in the post, one can just see the logs flying by with the exact queries being executed.

It is, but a developer that's using ORMs because they're scared of SQL tends to be the same type of developer that will decide not to look at those. I'm not dismissing ORMs - I use Sequel (the Ruby ORM) for almost all my database access. But I've also seen enough people use ORMs as an excuse to pretend they don't need to understand SQL or understand the database to understand why some people look at ORMs with suspici…

I don't know, I find that a lot of the people that write the most inefficient ORM code would also not spot the inefficiencies in SQL queries either.

On the flipside, I've seen a lot of people that can write efficient SQL queries, but then turn around and ruin their performance with a bespoke code model that inefficiently calls those SQL queries and poorly leaks the memory and database connections while doing so.

A good ORM makes it rather easy to fix an inefficient query of a fellow developer or previous self (almost all of the examples in this article are effectively one-line changes; many of the biggest performance gains I've made in ORM usage have been removing code and/or making it more readable), but fixing the mistakes of a bespoke model can be a huge challenge with a lot of surprises.

Post reply on HN