How not to structure database-backed web apps: performance bugs in the wild • Hacker News Board

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

#81

Post by mikekchar » Thu, Jun 28, 2018, 9:02 AM UTC

shangxiao wrote: post #14

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

ORMs are good for 90% of your use cases, the rest I'm happy to use SQL for. I could attempt to deconstruct the ORM problem, but Martin Fowler had a nice article [1] on replying to "ORM hate" a few years back which is worth the read for folks that question the usefulness of ORMs (and I believe questioning things is a healthy thing to do!) [1] https://martinfowler.com/bliki/OrmHate.html

As much as I like Fowler, he's missing an important option in his description: don't try to do object relational mapping generically. Consider that you have business model objects. In the UI, you create a "view" of that data. Of course the UI view is not related in structure to the business model object. That would be absurd. We need to use collections of model objects and present the data in a flexible. Just as absurd would be to build a framework for automatically mapping the data in the model layer to the UI view.

Now consider that your DB is just like your UI. It's a view of your model objects. Just in the same way, there is no reason why your model objects should be related structurally to your DB representation of the data. And in exactly the same way there is no particular reason why you should have a way to automatically map to and from your DB layer to your model objects.

In many cases, it will be less complex to build a bespoke OR mapping rather than try to find a system that will do it generically.

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

#82

Post by knesek » Thu, Jun 28, 2018, 9:05 AM UTC

When I was inexperienced I feared ORMs because of the negative performance impacts I've read they could have. I constantly worried about what would happen if the amount of data increased and I hit ORM induced problem that I could not resolve without major rewrite of data access layer. However, whenever I've actually hit those problems in production, I found the similar thing the authors of the article did - ORM induced performance problems can be fixed by 1-5 lines of code changes, if you knew where the actual problem was. In fact, I learnt that there's no "ORM induced performance problems", there are only problems induced by lack of understanding of how ORM works.

As for the knowing where the performance problem is, I find that skill (that I think should be basic) is in fact very elusive in the engineers I encounter. When I interview people, even in what would be upper intermediate to senior level in terms of years of experience, alarmingly small number have ever done or could do (or even would do) performance profiling like described in the article. Yet they do describe how they changed this ORM for that ORM, this DB for that DB, this language for that language, in the name of higher performance.

I've seen or heard of:

- Teams spending weeks exchanging SQL DB for No SQL DB because of unsolvable performance problem. When hitting the same problem with NoSQL DB, they find that addition of a simple index is solution in both cases

- Teams spending weeks exchanging Hibernate for OpenJPA in a complex application, because of performance, without doing any performance analysis, because they've read article that says Hibernate is slow

- Teams choosing complex architectures they don't really understand, for performance reasons, without being able to articulate performance requirements of the system they're building

These days, whenever someone mentions performance as a reason for anything, I judge their competence based on their response to the question "And how are you measuring and monitoring it?"

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

#83

Post by girvo » Thu, Jun 28, 2018, 9:06 AM UTC

ianamartin wrote: post #34

sfvisser wrote: post #27

Earlier quoted context omitted.

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?

Why not both? Truly, we should work to have better abstractions. We shouldn’t glorify making our own lives more difficult. And we shouldn’t glorify ignorance either! It’s not an either/or dichotomy here

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

#84

Post by weavie » Thu, Jun 28, 2018, 9:09 AM UTC

adrianmsmith wrote: post #69

weavie wrote: post #62

Earlier quoted context omitted.

I think one of the problems with SQL queries is that as far as your application is concerned they are just strings. There's no typing information or even syntax checking. Also if you do something like 'select * from' then there the results returned aren't deterministic. If you scatter these throughout your code and then the database schema changes, you have a hell of a refactoring job to make sure everything still wo…

You can get the best of both worlds by using e.g. jOOQ in Java (allows you to write e.g. db.select(MY_TABLE.MY_COL).from(MY_TABLE) where those values are generated from the database therefore they exist and are of the right type. It maps 1:1 to the SQL statement that gets executed so there's no magic e.g. extra n+1 queries being introduced without you noticing. But if you change your schema, re-generate, and immediat…

I wasn't familiar with jOOQ before. It looks pretty comprehensive.

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

#85

Post by manigandham » Thu, Jun 28, 2018, 9:11 AM UTC

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…

Not this again. ORMs are tools, basically dynamic code generators that run SQL and map the results to in-memory objects, and vice-versa. Some are simplistic and others are incredibly advanced, and the code itself is usually faster than your own sql->objects logic that you would write otherwise.

The issues with performance are almost always with the way the tool is used, like choosing a bad algorithms or the wrong data structure for a certain situation.

Lazy-loading a large child object in a tight loop over 100s of entries will always be slow because it's an improper approach, and nothing other than knowledge and competency will resolve that. It's rather amazing ORMs can be so controversial when the real issue is the developer, which speaks to the far bigger problem of expertise and quality in this industry.

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

#86

Post by sudhirj » Thu, Jun 28, 2018, 9:13 AM UTC

adrianmsmith wrote: post #73

doesnt_know wrote: post #29

Earlier quoted context omitted.

> I have never seen ORMs as tools for completely abstracting away the database. Some are advertised that way. Entity Framework Code First, Migrations etc. At our place, our DB dev team is larger than our api team, which in turn collectively dwarfs our front end teams. ORMs in this sort of environment have never really been given a chance, but I feel like it would be easy to justify them in many situations...

> Some are advertised that way As an example of this, the influential DHH of Basecamp blogged saying just that: https://m.signalvnoise.com/conceptual-compression-means-begi... "Basecamp 3 has about 42,000 lines of code, and not a single fully formed SQL statement as part of application logic!"

Given that these are the people who wrote the Rails ORM, you'd expect that they know how to use the ORM to generate high quality SQL. Which is actually quite doable - in Rails / ActiveRecord you're much better served by knowing what happens for every ORM call, and the default development log prints every generated SQL query as well. Think it now also provides alerts when the queries are slow.

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

#87

Post by girvo » Thu, Jun 28, 2018, 9:13 AM UTC

guiriduro wrote: post #64

Wouldn't it just be better to invert the relation (sic) between App-ORM and DB, and have everyone better understand what data modelling and a DBMS is, then write SQL and some reverse-ORM to expose App services in SQL? E.g. EXPOSE SERVICE(REST, GraphQL) BillOfMaterials (VARCHAR arg) AS (SELECT ... WHERE ... = arg etc.)

Yep! See here[0] and here[1] for that idea done with Postgres

[0] https://postgrest.org/en/v5.0/

[1] https://www.graphile.org/postgraphile/

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

#88

Post by Nokinside » Thu, Jun 28, 2018, 9:14 AM UTC

grey-area wrote: post #54

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 use an ORM. I don't perform queries like that because the ORM makes it easy to build complex queries, joins, limiting the data returned etc, then execute them in one query, it's a convenience, not a straightjacket. The problem is not ORMs, the problem is just people not thinking about the resources their query over some data takes, and trying to do things in memory that are better done in the database (as in your e…

Few months back I was working together with a group of framework programmers from a large consulting company.

They seem to spend solid third of their time googling for how to write a code. Write code, hit a jam, google, find something in stackoverflow, ruminate between what is found, copy paste and try if it works. They don't know their framework well enough to just write a code.

I'm a more of a embedded software/hardware guy and manager, but I can sit down and write pivot pivot query in plain sql without consulting any references.

I'm not saying these guys are doing something wrong. It may be that in the typical consultant work where you have existing old frameworks, new frameworks and new stuff coming in constantly, there is less value to master the tools that you are using. You write some glue, then move on.

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

#89

Post by fuzzy2 » Thu, Jun 28, 2018, 9:18 AM UTC

TekMol wrote: post #52

fuzzy2 wrote: post #37

Earlier quoted context omitted.

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.

I say ORM is the correct choice in many cases I'm still undecided about this. Would be fun to compare some actual approaches. Which is your favorite ORM?

Currently I’m trying to use Dapper if possible. It allows me to write efficient queries manually and just maps the results to objects.

For simple queries though (single-table), Entity Framework is just as fast.

Because I’m mostly working on dashboards and stuff, writing to the database isn’t much of a concern.

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

#90

Post by TekMol » Thu, Jun 28, 2018, 9:24 AM UTC

fuzzy2 wrote: post #89

TekMol wrote: post #52

Earlier quoted context omitted.

I say ORM is the correct choice in many cases I'm still undecided about this. Would be fun to compare some actual approaches. Which is your favorite ORM?

Currently I’m trying to use Dapper if possible. It allows me to write efficient queries manually and just maps the results to objects. For simple queries though (single-table), Entity Framework is just as fast. Because I’m mostly working on dashboards and stuff, writing to the database isn’t much of a concern.

What is the code to get the number of cars with expired insurence when using Dapper?