Not an enemy yet, but I’ve just debated with a senior dev that said stored procedure is faster, safer, and works better than entity framework

I agree with faster (only a bit) but the rest is just bs

@irene scalability, more stable, and other stuff he mentioned. I’ve never faced these issues before plus developing process is faster with entity. Most of the dev here not using c# with oop style, the stuff here is still traditional datatable format, no class whatsoever

Fuck entity frameworks.

@Npstr oh that’s new, what’s your problem with it?

Like most things in dev, they both have their place.

@Robinha well the way that he came at me was more lik “I don’t like new tech and I’m comfortable with this”

@devTea My personal problem with ORMs is that they are always incomplete, and often seduce devs to write inefficient queries.

Any ORM which allows you to easily write raw queries and still hydrate objects effectively is OK though...

And stored procedures are cancer. Difficult to test, not transparent.

@bittersweet what would you prefer for writing queries then?

@devTea well, I'd still use an ORM, just scrutinize what it does.

In some cases user.comments.sum('likes') might get you the same results as "select count(distinct likes.id) from comments join ... where user.id = ..." etc — but it's worth it to closely analyze what the ORM does...

Because it might drag half a database to the webserver just to sum up a column.

Right tools for the right jobs I guess. Even stored procedures can have their place, for example to add an extra layer of constraints to guard database integrity.

@bittersweet well the way it‘s used here is for every transaction or operation that connected to db

Using stored procedure for everything is bullshit, but there are things you cannot do with an ORM (or not efficiently/easily).

A few examples I had to implement in stored procedures:

- recursive query

- having a trigger that can be bypassed by a procedure (for an exceptional case)

- getting the type of a row in a base table that have several inherited tables

@devTea @Fradow

Well as an example where ORMs don't offer a solution... I often need an answer to a question like "find all teachers who have become ill this morning, and for each find a teacher who has the same mastery of the subject matter, and does not have an overlap in their planned courses -- and match them all up so the total distances all teachers travel is minimized"

It is tempting, but super slow to mass-select all teachers, each with their own timetable schedules. And complex things are doable in raw SQL. But the ORM isn't very suitable for subqueries and relational division (see things like: https://red-gate.com/simple-talk/...).

The ORM is super convenient for "Hey, I want to create a user. OK, now that I have a user, I want to create/read/update/delete one of their articles". That where you SHOULD use an ORM.

But it rarely works well when you need complex aggregates, statistics and matches.

The one-and-only valid argument (not to be confused with a lazy excuse) for using SPs over an ORM is that since there's no need to compile them into binary, they can be modified during production, outside of any version control scenario, or in fact on a live DB...
I guess some people would want such a challenge in their lives, but I'd argue that it is a terrible idea and should NOT be considered an advantage!

@hamido-san you can do the same with code and FTP.

But yeah, both are a terrible idea anyway.

ORMs suck. SQL is a 4th generation language and does not lend itself well to being abstracted into patterns of 3rd generation languages. But that's not all: most ORMs also try to support many databases, and therefore make it hard to use database-specific features. Sure some ppl love ORMs - because they are their job security. ORMs might make a few already simple use cases even simpler, but I've never seen one that was actually helpful with more advanced queries, on the contrary - instead of learning SQL properly (a valuable and transferable skill) you end up learning the quirks of some shitty framework entirely useless for all projects that use a different one.

Here's a great link about one very notorious ORM: https://hackerfall.com/story/...

@Npstr that post is fun to read

The guy who joined before I leave my last company .. Removed all the EF in the last 2 projects I completed, and replaced it with SP’s .. I wasn’t very convinced but he can do what ever he want, I left them!

@Npstr The fun part is that tutorials are always "let's make a to-do app using React, a rest API, this ORM, and MySQL"

It's never "Let's build a financial analysis and statistics dashboard using this ORM without using half a gigabyte of RAM per request".

@bittersweet is there a way for me to read how much resources ORM process used?

@devTea I think it depends on the language... I primarily use PHP/Laravel, so I use xdebug & debugbar for local profiling, blackfire.io and newrelic for testing & production.

@bittersweet any reason to not use ORM other than complex queries?

@devTea

Nope, not in my opinion. If you make a Rest API where each resource has a read/create/update/delete operation, and you use one single backend language, you should absolutely use an ORM. It makes the DB queries much easier to read, and much safer against injection tricks.

But check if the ORM has some print(ormBuilder.toSQL()) function, as it's very educational to check HOW the ORM builds queries, and how that artificially generated syntax affects performance.

@bittersweet thanks for the response, I’ll note that down

@Mbithy please help me explain it to them why using SP for EVERY sql transaction is bad