BuT wHy Do YoU uSe An ORM? YoU sHoUlD wRiTe RaW SQL fOr P e R f O r M a N c E

Sorry, but my orm makes it more save and faster.

@stop

but you better chose a good ORM. I had used the ORM when you change the schema , it drop the table and create it again.

Because this pisses me off like all hell when you're writing large queries.

Select(field,field,field).from(table).join(table2).join(table3).where(table3.id=1);

@C0D4

Essentially you are sacrificing performance and control for convenient.

I like to write store procedure,function,views,--etc so I can fine tune the performance.

Tell me one way on which an orm is better than an sql query

An ORM extends SQL and complements it rather than fully replaces it. It's not like ASM where you can get away with completely ignoring it if using C or higher level.

There are things that are way more convenient with SQL than ORM. The level of convenience provided by each is mixed based on use case. It's not all about performance.

I've made a few ORM libraries of varying levels of completeness myself often to cater to various use cases where they can work really well but for non-trivial cases SQL or some other query language becomes superior.

The special points of contention among people who don't like ORM are:

1. Many, even some of the most popular ones such as doctrine and eloquent aren't well engineered by my standards and can undermine even when they should work well.
2. People trying to push ORM on others and other high level abstractions tend to be evading having to learn the nitty gritty but high level abstractions aren't always a full substitute.

@mr-user Sometimes that's a hard problem. If you rename something there's no way to know for sure when doing a differential if it was renamed or removed and another table created even if the only difference is in name.

Rather than guessing, the simplest assumption is made, that the table that disappeared was dropped and the table added was created.

Migrations always need to be checked by hand. Say way as merge conflicts need to be fixed by hands.

The only way for an ORM to get it right might be to provide the same kind of thing as SVN or GIT for that case where you would tell the ORM to move the table and for it to maintain a record of that for migrations.

For example, ORM rename table_a table_b. A lot of ORMs don't provide this and with annotations it might be a case of do not want the ORM loading and saving classes.

The ORMs I make work internally so that's possible but its not worth it if I can just check and fix the migration instead.

If its Android ,then room ftw💪

@molaram ORM translation isn't 121.

https://en.wikipedia.org/wiki/...

ORM is one high level abstraction but SQL or other remote commands or remote command structures are high level abstractions as well! ORM typically reduces those high level languages to a restricted less expressive form!

This problem isn't always SQL versus OOP. OOP, basic methods, object graphs, fluent interfaces, etc cant express everything conveniently SQL or not.

Everything your describing can also be very resource intensive. We're not talking bean counting though that can happen. Sometimes the difference between ORM and SQL can be the difference between using 1TB of RAM or 1GB of RAM. ORM can take up a million hours of CPU time where directly writing SQL might only take up a thousand.

Which AWS bill would you prefer? $1000 or $1000000?

@mr-user the best ORM i used was Django ORM.
It generated the Migrations, detected the changes and was on 100% correct, when i used it. It also detected migrations with changes to nonnull (and asked for defaults) and was database-neutral. I never saw such an sophisticated orm. The connection with django was more beneficial than problematic.

Sounds like the last company that I quit

There is a time and place for everything.

That statement holds true for the ORM vs No-ORM debate.

I've worked at companies where raw SQL performed worse than ORM-generated SQL, just because a lot of the devs were pretty junior and had no idea how write performant SQL.

There isn't a right or wrong - you should use the right tool for the job and sometimes the ORM _is_ the right tool for the job.

If used correctly orm is A LOT faster. Thanks to L1C and L2C

@netikras are you talking about cashing or GPS signals?

@hjk101 caching in-instance and caching across-instances

@rhymiz especially juniors should using an orm. Raw SQL and juniors makes only Problems. That is my expierience(as the junior).

@netikras ORM still uses SQL. There's nothing ORM can do that SQL can't.

It's a misnomer as no one really means SQL on its own but SQL + some application programming language.

ORM is someones personal idea of that that should look like and often they block you off from the SQL side of the equation. Those that don't block you off entirely tend to struggle very hard to play nice together.

ORM being faster than SQL makes no sense when its still SQL. This seems to be a common mistake. For example people thinking VDOM is faster than DOM when it's still DOM.

@RANTSMCPANTS not true.

Yes, orm uses sql to communicate with data repository. Does sql have a transparent cache? Does sql share entities between different applications and keeps them in sync at all times, saving you thousands of interactions with db every minute? Does sql prevent you from writing 'select * from' which is hell of an overhead? Does sql validate ddl to keep it in-sync with what app expects? Does sql automatically build/update ddl to your app needs [not perf related]?

The only perf downside orm has is stupid mapping with overused eager fetching. That's why I said 'if used correctly'

orm is much, MUCH more than just sql and mapping to entities :)

@netikras You can implement transparent caching or whatever you need with SQL.

ORM is really just someone's specific library implementation. If you have simple needs and the ORM is well written then it might work out.

I've never encountered an ORM that does everything well though.

Quite often, they're catering to portability you don't need and worse is that they often conform the the lowest common denominator.

If I'm not that concerned about portability which is very often the case then ORM is notorious for not taking full advantage of any given DB system.

I tend to find people who want to turn on transparent caching at the click of a switch don't care about consistency very much whether they should or not.

@netikras have to go with @RANTSMCPANTS on this one. You can add a caching layer at every level of abstraction. You don't need an ORM to build a caching layer or other logic on your database. Some ORM's might facilitate it out of the box or make it easy to insert application specific performance improving logic though.

@netikras Wait a minute.

> Does sql share entities between different applications and keeps them in sync at all times

What's that? It sounds like the basic function of a database or the way you describe it distributed SHM or p2p caching.

Are you sure you're not talking about memcached / redis or something?

I made my own for quick queries, but still use RAW SQL for complex lookups.

@molaram okay, but of you are working on a large scale project where databases get so complex you cant write queries, you should hire a dba and not let the devs handle that part.

@RANTSMCPANTS that's caching. Last time I was making a l2c cache for hibernate using infinispan. Took me no more than a few hours to set it all up so that all the 48 pods of the same replicaset would stop bashing the database: a shared cache with all the synchronization, critical section handling and everything.

It would've also taken hours to implement it with sql ofc. But on top - a few more weeks of hunting down bugs of critical section being mishandled.

If I got it right hibernate's l2c is event-based and updates a local cache on all the replicas once either of them notices a change. Which effectively means that once one of the replicas fetches an entry from the repo or updates it, it immediately shares it with all its peers so they wouldn't have to query cache every time b4 hitting the repo.

Orm [at least advanced ones] are far more than just reflection+sql :)

i get your point though. Using such a mechanism might be an overkill for projects which are exposed to low tps [100 or less] and reflection would be a performance pit [although not quite as well -- thanks to l1c and queries' caching and aggregation]. But for large scale applications dealing with 500 to 10k tps or more I would not risk using anything but orm. A repository would be in hell of a trouble, not to mention a split nanosecond exposing a critical section handling bug would be of tremendous impact.

I'll be honest, whenever I sEe wRiTi Ng lIkE tHiS I instantly tend to disagree with whoever is posting it.

@AlmondSauce everyone is free to disagree. That's why it's a rant.

I wrote it that way because folks on either extreme of the argument are wrong.

Using the right tool for the job is more important than any personal vendetta against using or not using an ORM.

For basic CRUD operations, for example, the ORM has its place. It also serves as a safety net when you have a ton of juniors on your team.

For parts of the code base where you need raw performance, you can dive into writing SQL - for example, in a reporting pipeline.

Both of these approaches can live side by side in the same code base.

@rhymiz The problem with ORM and usually in association with frameworks is it has become the entrypoint for idiots, who have DK and go around bullying those not as restricted to a very specific toolkit as they are.

To normal people, libraries, including ORM are just tools in the box, you don't think about them until you have a worthy opponent for them. To imbeciles ORM has to be brought up every turn without any specific problem in sight.

Some platforms have good ORM libraries but rarely thoroughly so because these things are huge complex lumbering systems with tradeoffs.

To add insult to injury most of these people also insist on awful libraries and frameworks. For example I do a lot of PHP. Not a great but if I need something simple it's quick and easy.

PHP has an absolutely awful ORM library called Doctrine. The name is apt. It might seem to make sense to give idiots ORM because they can't be trusted with SQL. In most cases it turns out they can't be trusted with ORM either.