14
you are viewing a single comment's thread
view the rest of the comments
view the rest of the comments
this post was submitted on 29 Mar 2024
14 points (88.9% liked)
SQL
469 readers
1 users here now
Related Fediverse communities:
- #sql on Mastodon
- #postgresql on Mastodon
- c/PostgreSQL on programming.dev
Icon base by Delapouite under CC BY 3.0 with modifications to add a gradient
founded 1 year ago
MODERATORS
The SQL queries I’ve seen almost every ORM create are highly inefficient compared to a good query designed by hand. That’s where I’ve always seen the delay get introduced. You end up spending lots of cycles on the query so the delay looks like it’s within the DB but when replaced by a custom query the bottleneck goes away. Quite often a quick fix is replacing the query with a store procedure or view and letting the devs work off of that.
I have to just respectfully disagree. Perhaps you had an actual database engineer on your team who was in charge of the sql (in which case 100% yes absolutely use sql!)
But typically backend devs aren't database engineers and they have no idea how to compose a good sql query, let alone how to optimize it or test its execution plan.
I've seen way way more absolute clusterfuck garbage queries that take way too long to run that were hacked together by BE devs.
Views are fucking awful imo. It's yet another entire layer of abstraction that deeply obfuscates what is actually running and/or happening.
The entire principle of shit outside my codebase that I can inspect with my LSP causing side effects to my logic is just a nightmare to maintain.
The moment you can have the exact same application behave differently purely because different stuff was or was not put on the db it was pointed at, it's an absolute cluster fuck to maintain.
Stored Procedures only should be reached for under one circumstance imo, and that's when you need to use recursion on your DB.
IE if you have perhaps a parent/child self FKd table to create a hierarchy tree with unknown depth... You'd want to traverse it recursively which likely would want a stored proc.
But aside from that, I just can't get behind breaking up the backend into effectively having 2 distinct layers of truth to its behavior.
I want all my codebase in one place, and in one language, under one language, through a single LSP.
I have had Database Engineers and done it myself. If you run any ORM created SQL queries through a profiler and look at the execution plan you’ll see it’s an absolute mess. That’s why I said it doesn’t scale. Sure it’s good for small things but I’m working on projects that have millions or rows and multiple joins. At that scale it just starts to fall apart. Having good raw queries will beat out an ORM every time at scale and that’s why I hate them. You want to use it for a small quick project, go for it. You’re trying to work at enterprise scale, get a DBA to make you actual queries.
I've never had this experience with Entity Framework, full stop.
It sounds like either the devs were abusing the ORM and using it wrong to make it generate garbage, or, you were just using some very poorly written ORM.
Should the BE Dev be touching the data if they don't, you know, know how to work with the data? No: SQL Developers or Data Engineers should create data they can access and use. The design of the database is entirely separate from the design of the application. They do not need to be related, and usually should not.
Again, should the BE Devs be touching the data, then? No. If they don't know what they're doing (which is writing queries to handle data), then they shouldn't be messing with the data. The Data team should be messing with the data, and serving it to the BE Devs.
You must not be a SQL Developer or Data Engineer.
You're definitely not a SQL Developer or Data Engineer.
I had to read this several times to make sure I understood, because I would absolutely and immediately fire any Data Engineer working for me who developed something like this, and I would shun and or quit the company or project team that insisted on developing an application or process that required it, especially if they did so without consulting their Data team.
This is completely unrealistic. It speaks to a lack of industry experience and is down-right selfish. No hiring manager will ever take you seriously if you say you only use one language. You need to know several to get by. And that's because each one has a different purpose. HTML renders web content, CSS makes it pretty, JavaScript makes it think, PHP makes it explode, Go makes it go, JSON helps it share data, SQL manipulates the data, Shell/Bash updates systems, etc. Some of these are markup languages, some are stylers, some compute and run functions, some explain data, some manipulate data, some give system instructions. They all have a different purpose, are used in a different layer of the stack, and require a different expert (with the exception of some Full-Stack Wizards) to implement.
Saying you want one language is the most bonkers thing I've read on the internet this month. Don't travel the world; you're gonna have a bad time.