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

261–270 of 360 posts

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

#261

Post by zimpenfish »

scarface74 wrote: post #241
zimpenfish wrote: post #229

Earlier quoted context omitted.

If you have to log the generated SQL to understand what's happening, you're already behind the curve. And then what do you do if the ORM is generating junk? If the answer is "use a querybuilder/handcrafted SQL for that one", what's the point of the ORM in the first place?

How often is the ORM generating junk? To say that the ORM is useless because occasionally it doesn't generate performing code (with EF and Linq more often than not it does generate performant code) could be applied to any high level construct. But I don't see people giving up modern languages to go back to writing everything in Assembly or even C. Yes I optimize when my automated performance testing/stress testing, t…

> How often is the ORM generating junk?

From what I've seen (couple of home-grown ones, Class::DBI, ActiveRecord), "more often than you want".

(I'm willing to admit they may not be class-beating examples. :)

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

#262

Post by zimpenfish »

icebraining wrote: post #257
minitech wrote: post #125

Earlier quoted context omitted.

> Good ORMs help tremendously with maintainability and security. ORMs don’t help with security compared to query builders or even typed text.

Security is not just about injection. The ORM we use automatically applies security access rules to the queries, for example.

> The ORM we use automatically applies security access rules to the queries, for example.

But surely that's better handled in the database itself?

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

#263

Post by Mouse47 »

mattmanser wrote: post #196
rjbwork wrote: post #60

Earlier quoted context omitted.

I've been writing C# professionally for ~5 years at this point. While I was initially quite infatuated with LINQ2SQL and EF, I have gone through the same situation as this fellow. I just write SQL in-line using Dapper for parameterization/data mapping, and use stored procs when I need some of the more arcane features of SQL (merges, CTE, etc).

I've been writing C# professionally for ~12 years at this point. I'm extremely comfortable with SQL, the first startup I worked for for 3 or 4 years in the mid-2000s did amazing things with it and was extremely anti-ORM. We did things like write SQL that would automatically get translated into XML, which we'd combine with xslt to create dynamic pages. Yes, I've hit major problems with the EF (including one on Friday…

>Lazy Loading can also screw you, but again, it's just wonderful when you use it right.

That's funny - the first thing I do when starting a new project is turn off lazy loading globally. I find it hides poorly-performing code until it's causing problems; the equivalent code without lazy loading usually just throws an exception.

Granted I've only been in the industry for 3 years, so /shrug

Also, question: you mention code-first and migrations... do you think you'd rather use SQL for your schema definition/migrations if the tooling was better? I find SSDT doesn't quite cut it :(

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

#264

Post by zkomp »

icebraining wrote: post #255
zkomp wrote: post #129

Earlier quoted context omitted.

But why do you need these containers of data, when you could have more direct access to both the data itself and the whole set (not individual objects, nor collections of objects) I dont like ORMs but did struggle some years trying to use them which imho was a detour. SQL and stored procedures in plpgsql is so much better, easier to maintain, easier to reason about etc.

How would you calculate the average of the sum of three columns (AVG(A + B + C)) which are chosen by the user at runtime?

I personally would write a simple plpgsql stored procedure, but I would not trust user input and only allow a defined set of colums from a defined set of tables.

you can have lots of dynamic sql but that might become a rabbithole, just as with an ORM. It sounds like a problem you shouldnt have, now throwing an ORM at such a problem... might lead to even more strange issues down the road...

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

#265

Post by cpfohl »

majewsky wrote: post #259
oceanswave wrote: post #240

Earlier quoted context omitted.

If type safety is so great, why isn’t sql statically type checked

SQL is statically type-checked, at least in Postgres. postgres=# SELECT * FROM domains WHERE id = 'foo'; ERROR: invalid input syntax for integer: "foo" LINE 1: SELECT * FROM domains WHERE id = 'foo'; ^

Not just PostGres. That's standard SQL. Columns have data types. Tables have schemata. Schemata are very strongly typed. Can't insert a 'full_name' column into a table without such a column.

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

#266

Post by scarface74 »

zkomp wrote: post #256
scarface74 wrote: post #212

Earlier quoted context omitted.

Every single implementation where I've seen "business logic in the database" has been an unmitigated disaster. On the other hand, having well factored microservices (out of process) or in process modules have worked out really well with modern devops and software engineering principals - easy push button deployments and rollbacks, unit testing, A/B deployments, etc.

I think bussiness logic in the database has prevented disasters in the projects I have worked on. I honestly dont see how it could have been solved better... It probably depends on the domain/problems. My experience is with transaction heavy financial systems or similar, with web frontends, microservices sprinkled around in different languages... The web app or java worker should be allowed to focus in its problems,…

And what's stopping you from having a tightly controlled interface with a REST Api that is easily deployed, rolled back, unit tested, source controlled and deployed?

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

#267

Post by scarface74 »

zimpenfish wrote: post #261
scarface74 wrote: post #241

Earlier quoted context omitted.

How often is the ORM generating junk? To say that the ORM is useless because occasionally it doesn't generate performing code (with EF and Linq more often than not it does generate performant code) could be applied to any high level construct. But I don't see people giving up modern languages to go back to writing everything in Assembly or even C. Yes I optimize when my automated performance testing/stress testing, t…

> How often is the ORM generating junk? From what I've seen (couple of home-grown ones, Class::DBI, ActiveRecord), "more often than you want". (I'm willing to admit they may not be class-beating examples. :)

I meant a good ORM. But then again, my definition of a good ORM is an ORM with a language that treats queries as a first class citizen. EF with Linq doesn't really act feel like a separate framework since Linq and Expressions are built into the language.

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

#268

Post by NoGravitas »

bmarkovic wrote: post #159

It's "Active Record" style ORMs like Hibernate that are the culprit, and the way many developers utilize them to avoid any contact with the realities of RDBMs which leads to data access antipatterns which lead to poor performance (multiple needless queries per request etc.). Another thing people need to really give up on is the pipe dream of switching databases -- you're not going to do it. I've never seen one single…

I think it's misleading to think about switching databases on an ongoing application as the use case for database independence. More important use cases:

1. Using SQLite for unit testing and a real RDBMS for integration testing, acceptance testing, and production. 2. When you are writing a library that will be used by different projects, not all within a single organization (e.g., a Free Software project). 3. When you are providing a product that the end-user may want to use with different choices of database (e.g. forum software, Nextcloud, etc.)

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

#269

Post by mattmanser »

Mouse47 wrote: post #263
mattmanser wrote: post #196

Earlier quoted context omitted.

I've been writing C# professionally for ~12 years at this point. I'm extremely comfortable with SQL, the first startup I worked for for 3 or 4 years in the mid-2000s did amazing things with it and was extremely anti-ORM. We did things like write SQL that would automatically get translated into XML, which we'd combine with xslt to create dynamic pages. Yes, I've hit major problems with the EF (including one on Friday…

>Lazy Loading can also screw you, but again, it's just wonderful when you use it right. That's funny - the first thing I do when starting a new project is turn off lazy loading globally. I find it hides poorly-performing code until it's causing problems; the equivalent code without lazy loading usually just throws an exception. Granted I've only been in the industry for 3 years, so /shrug Also, question: you mention…

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.

It's generally very cheap to do a single item query with no joins (as in a nanoseconds db query, yes, nano), and it only has to do it once, if you re-use that object again anywhere else, it's already in the context so it doesn't have to go to the db. Even doing them hundreds of times can be super cheap[1]. Add to that it only has to load each item once you can make intelligent decisions about what to .Include and what to lazy load.

[1] Caveat, Azure db connection latency often sucks so this isn't completely true, we had 5-6ms instead of the <1ms you'd expect. Presently at about 2-3ms. This is the fault of Azure and not lazy loading though. Causes a problem when you make hundreds. 100 lazy loading calls each taking 6ms would add 600ms, or 1/2 a second, to a request.

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

#270

Post by paulryanrogers »

jcadam wrote: post #258
andybak wrote: post #177

Earlier quoted context omitted.

> ORM: You ask for something and you don't care _how_ its fetched. Try this: > ORM: You ask for something and because you've researched and found a well-written, quality ORM you trust that it will create a sane query. or possibly: > ORM: You ask for something and you accept the tradeoffs vs hand-written queries but you're content that it's the correct tradeoff for your use-case. There's plenty of places for bottlenec…

> ORM: You ask for something and because you've researched and found a well-written, quality ORM you trust that it will create a sane query. Ha. More like: ORM: You've just joined a project already using an ORM selected by an 'architect' that no longer works here. Everything is fine until you start testing your system with a database sufficiently populated with real-world data. You and the DBA spend the next next 6 m…

IME, it should never take more than a few hours to run down why the ORM made such a query. At a minimum most RDBMS's have query logging and can explain queries.

Ironically the last time I had a big ORM performance problem was Hibernate eager loading all joins. Diagnosing and fixing it didn't take more than an hour. (Though we did have a very experienced DBA at the time.) YMMV

Post reply on HN