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

191–200 of 319 posts

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

#191

Post by acdha »

dagw wrote: post #176
acdha wrote: post #164

Earlier quoted context omitted.

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 :)

Seeing how much business people like that got was definitely an educational moment about the efficiency of the IT market ;-)

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

#192

Post by gldalmaso »

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…

> But wasting a month tryig to write native Sql queries can hurt your project a lot more.

Unless it would be the first contact a developer has with SQL, I really don't see how difference in time taken to query the database with an ORM vs SQL would be anything more than a couple of minutes, maybe an hour if you really have a hard time with it or your data model is really convoluted.

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

#193

Post by coldtea »

manigandham wrote: post #167
coldtea wrote: post #112

Earlier quoted context omitted.

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

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

I don't believe in choices, people are flimsy. I believe in creating an environment that encourages good behavior.

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

#194

Post by bpicolo »

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)

SQLAlchemy supports all of the above - in general it's really terrific about being extensible enough to support the corner cases.

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

#195

Post by brightball »

I experienced fixing most of thes first hand. I think 90% of the issues come from developers who haven’t worked directly with SQL enough to understand what the ORM is doing.

The experience of building apps with hand coded SQL, while not good for a project, is an excellent teacher.

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

#196

Post by blattimwind »

latch wrote: post #23

I've worked on moderately busy backend platforms (~10K-20k rps handled on a ~4 e5-2650 and aiming for 5ms 95p response times). It greatly depends on what you're doing, but for the majority of systems which are read heavy (and that most certainly includes "dynamic" sites like Amazon or Wikipedia), I hold to two major beliefs: 1 - Have very long TTLs on your internal cache servers with a way to proactively purge (messa…

0 - Caching antipattern 101:

    key = calculate_cache_key()
    if not cache.has(key):
        data = expensive_calculation()
        cache.store(key, data)
    else:
        data = cache.get(key)

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

#197

Post by Balf »

Rafert wrote: post #152

Interestingly their code checker is just a bunch of regular expressions: https://github.com/hyperloop-rails/static-checker - I would've expected custom Rubocop rules. Rubocop already knows about `where.first? => find_by` for exmple: https://github.com/rubocop-hq/rubocop/blob/master/lib/ruboco...

They suggested naïve sequential string replacing for html templating, so I would absolutely expect a bunch of regular expressions.

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

#198

Post by blattimwind »

gldalmaso wrote: post #192
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…

> But wasting a month tryig to write native Sql queries can hurt your project a lot more. Unless it would be the first contact a developer has with SQL, I really don't see how difference in time taken to query the database with an ORM vs SQL would be anything more than a couple of minutes, maybe an hour if you really have a hard time with it or your data model is really convoluted.

This really depends entirely on what your application is doing and how it generates and processes queries. Some things are just annoying to solve without an ORM / "query builder" / SQL-AST (unless you write a small ORM yourself).

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

#199

Post by blattimwind »

mnm1 wrote: post #60
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…

Even if you get 1 hit / day and that takes a long time to return, the ORM has failed your one customer. Inefficiency exists even at small scales with ORMs. If your developers don't know how to write SQL, let them learn. Or fire them if they won't.

> Inefficiency exists even at small scales with ORMs.

In other words, you have no idea what you're talking about.

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

#200

Post by pier25 »

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.

Sure, writing SQL won't solve design problems, but some ORMs need multiple queries to deliver data with relationships, joins, etc, that could be done in a single SQL query.
Post reply on HN