Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What ORMs have taught me: just learn SQL (2014)

woz.posthaven.com

Post reply on HN
Print view View original

351–360 of 360 posts

Re: What ORMs have taught me: just learn SQL (2014)

#351

Post by collyw »

gjjrfcbugxbhf wrote: post #165
failedxyz wrote: post #127

Earlier quoted context omitted.

while ORMs can do quite a bit of optimization, they're still general query builders and can't construct the optimal queries for your use case. if you don't know SQL, or you don't know what's going on behind the scenes, your ORM could be performing much larger queries than it really needs to, costing performance and time

Most ORMs have way too write raw SQL. Furthermore someone using an ORM well will understand how their queries are mapping to SQL. Your scenario happens with people that either don't know or don't care. They will write crappy queries with any tool.

Sounds about right.

I built a moderately complex application in Django at a previous workplace, using the ORM for most things, until the queries were too complex for the ORM.

Another guy connected to the same database and built some graphs using PHP and SQL. Guess who had to help him write the SQL when the queries got too complex for him? The ORM user.

Re: What ORMs have taught me: just learn SQL (2014)

#352

Post by Mouse47 »

mattmanser wrote: post #341
Mouse47 wrote: post #310

Earlier quoted context omitted.

>We have far more problems with too many .Includes causing terribly performing queries with bad JOINs than lazy loading problems. Granted this code base is in a bit of a state and we have a complex order structure that can go like 10 layers deep, and ideally we're looking to go even deeper with complex pricing. You do an include with all of that and you're going to get a terrible query. Wait - are you "Include"ing th…

The example is too trivial, which is why it looks like it might be better. Here's a real world example, not even complete. A restaurant booking might have: - A venue associated with it - A user who booked it - A menu -- With courses (starter, main, dessert) --- of Menu items (steak) ---- with Menu item option groups (think, 'pick one of', 'choose at most 3', etc.) ----- of Menu item options ('rare', 'medium', or 'ext…

I see - so in your case you gain from the fact that many of the joins are likely to result in zero matches. And since you're joining on a nullable FK, you can tell in advance whether the record exists without a lookup - I ran a test and I verified that you pay a cost for the below join regardless of whether the FK column is null.

    select *
    from Person p1
                        --p1.Spouse is null for the record in question
        join Person p2 on p1.Spouse = p2.PersonId 
    where p1.PersonId = 42
(I understand why it can't do it in the execution plan, but I'm surprised it doesn't 'short-circuit' at runtime since the join predicate is trivially unsatisfiable for that row)

Your other benefit involves conditionally needing data. I will say it's not too hard to structure app code to avoid loading redundant/unneeded data in your email example, but it's certainly easier and more maintainable when property access is fundamentally linked to its actual retrieval - it's impossible for another developer to make changes to your version and 'lose' the efficiency, while the same isn't true for mine.

So it's less black and white than I thought...which it usually is :)

How do you feel about using 'explicit' lazy loading? E.g.

    PersonEntity person;
    if(IWantToSendEmail){
        person.Reference(x=>x.Email).Load();
        //use email info here
    }
This might be the best of both worlds...

Re: What ORMs have taught me: just learn SQL (2014)

#353

Post by agentultra »

steve-chavez wrote: post #343
agentultra wrote: post #316

In a recent project we went without an ORM. We took a tack similar to how PostgREST and PostGraphQL are structured. We use views in the public schema to build our objects. Functions constrain our mutations. Triggers respond to events and maintain consistency. It makes our web API code simple and hard to introduce errors that invalidate our customers’ data. Don’t miss having an ORM. Always seemed like more abstraction…

Why didn't you just use stock PostgREST, in which area did you found it lacking?(Curious)

The reasons were more social than technical. I think PostgREST is a wonderful piece of software and not lacking in any regard that I am aware of.

Re: What ORMs have taught me: just learn SQL (2014)

#354

Post by mgkimsal »

collyw wrote: post #349
mgkimsal wrote: post #298

Earlier quoted context omitted.

I like the idea of stored procedures. I get some of the value they bring. However, the few times I've been on projects where sprocs where the primary focus of logic/truth/app... it was always a pain. * The 'developers' weren't allowed to write the sprocs. We were at the biz meetings, but the DB guys were hardly ever there - their meetings were separate for some reason, but because devs were at the meetings, the devs…

Your complaints about stored procedures seem to be about the nature of the company where they were used rather than any inherent flaw with them.

I believe I pointed out that I realize it's a human issue more than a technology one. It was easier for some people to get suckered in to the power dynamics being played because "DBA" was already seen as more of black-magic art sort of thing, and those guys were the "real wizards" and so forth, so whatever they say goes. It's not been everywhere I've ever worked where sprocs were used, but it seems to have been at the places where "stored procedures are law".

Re: What ORMs have taught me: just learn SQL (2014)

#355

Post by antjanus »

3riverdev wrote: post #73

(Bias: I'm one of the Hibernate ORM committers.) Hibernate (and presumably any ORM) was never intended to be a complete abstraction of anything-SQL. Like others have mentioned here, an understanding of SQL must be had before using an ORM. The ORM is one piece to the puzzle, not a shield to prevent you from having to touch SQL. One pattern I typically use is a take on CQRS: Hibernate for writing/updating/fetching/dele…

I have my own views on this but I basically agree with everything you said.

I think ORM is a great way to translate tables into real objects that have their own methods and properties that may or may not interact with the DB. I think that's where the real power is.

But for performance-necessary actions, yeah, SQL all the way (or rather a query builder).

Re: What ORMs have taught me: just learn SQL (2014)

#356

Post by antjanus »

red_admiral wrote: post #226

My 0.02 BTC on the matter: Object-oriented programming 101 assumes that all your objects are in memory, in a graph, so you can do things like person.getFriends()get(0).getName() [assuming the person in question has >0 friends]. Each step in the graph is essentially a pointer dereference, costing a constant effort. (If your data is small enough to fit in memory, that's what you should generally be doing. People who us…

Your $365.80?

$354.38 is a lot of money to stake your opinion on. Are you sure $389.20 is the amount you're willing to invest in this?

Re: What ORMs have taught me: just learn SQL (2014)

#357

Post by cryptonector »

jeswin wrote: post #57

The post is from 2014, so I won't be too harsh here. The author's problem is with some specific flavors of ORM he's used, and shouldn't be generalized. Hibernate's expressiveness is/was crippled by Java itself. C# ORMs on the other hand are way better because they benefit from LINQ which adds queries natively into the language. Other more expressive languages have excellent ORMs as well. The objective of ORMs is not…

1. So does PostgreSQL... 2. Coding directly in SQL one first normalizes as much as possible, then denormalizes as much as needed to make desired queries performant. If you should need to refactor the schema, things like "rename prop" are trivial, and other things less so, but probably also not automated by most ORMs anyways. 3. Simple joins, sure, but need much more than that and the ORM gets in the way. 4. Well, SQL is lazy.

Re: What ORMs have taught me: just learn SQL (2014)

#358

Post by weberc2 »

collyw wrote: post #350
weberc2 wrote: post #283

Earlier quoted context omitted.

You haven't saved time compared to using a query builder though; in fact you've lost time due to the hit to readability and debugability.

How do you know this? You have no idea what bugs were encountered or how easy they were to fix.

We're conjecturing about hypothetical bugs. No one in this thread knows what bugs were encountered nor how easy they were to fix; they aren't real bugs. They're symbolic of the bugs that we encounter every day, which give us the experience on which we base our conjecture.

Re: What ORMs have taught me: just learn SQL (2014)

#359

Post by mattmanser »

Mouse47 wrote: post #352
mattmanser wrote: post #341

Earlier quoted context omitted.

The example is too trivial, which is why it looks like it might be better. Here's a real world example, not even complete. A restaurant booking might have: - A venue associated with it - A user who booked it - A menu -- With courses (starter, main, dessert) --- of Menu items (steak) ---- with Menu item option groups (think, 'pick one of', 'choose at most 3', etc.) ----- of Menu item options ('rare', 'medium', or 'ext…

I see - so in your case you gain from the fact that many of the joins are likely to result in zero matches. And since you're joining on a nullable FK, you can tell in advance whether the record exists without a lookup - I ran a test and I verified that you pay a cost for the below join regardless of whether the FK column is null. select * from Person p1 --p1.Spouse is null for the record in question join Person p2 on…

It actually is quite hard to do it and have re-usable code.

There are various different ways the same email might get triggered, maybe the booking came from an API call, maybe it came from a new booking form, maybe it came from a 'send reminder' button.

In all cases, I have a booking object that will be in a different state of being filled in. The underlying need for data for the rest of the request is very different. Some of them need a fully filled in booking, some of them need the bare essentials. Our "fully load this booking" function takes like 150ms, which isn't cheap and a significant amount of the time of that is DB time, which is again our most in-demand resource.

CPU/Memory is (generally) under-utilized on web apps and letting the EF do it's lazy loading thing is usually the best solution.

As for explicit lazy loading, it's inelegant and way more code. One thing we know for sure, more lines = more bugs.

I'm not saying turning off LL is a bad thing, if it works for you, but I semi-regularly have a SQL profiler running while developing so I see when it starts kicking out loads of queries un-necessarily.

Re: What ORMs have taught me: just learn SQL (2014)

#360

Post by scarface74 »

mgkimsal wrote: post #354
collyw wrote: post #349

Earlier quoted context omitted.

Your complaints about stored procedures seem to be about the nature of the company where they were used rather than any inherent flaw with them.

I believe I pointed out that I realize it's a human issue more than a technology one. It was easier for some people to get suckered in to the power dynamics being played because "DBA" was already seen as more of black-magic art sort of thing, and those guys were the "real wizards" and so forth, so whatever they say goes. It's not been everywhere I've ever worked where sprocs were used, but it seems to have been at th…

And it's because most developers can do sql but most "database developers" can't code. So of course they are protective over their code.
Post reply on HN