4 ms·

Of course, all of this goes completely out the window if your company mandates an ORM for database access, which i feel it’s pretty safe to say about 90% of com

Of course, all of this goes completely out the window if your company mandates an ORM for database access, which i feel it’s pretty safe to say about 90% of companies do. But that’s the point of ORMs. Disregard anything that makes your database different than any other, and any of its optimizations, so that you can pretend raw data fits an OO-paradigm and feel safe because you can go `customer.name = “dork”; customer.save();` and make anything more complicated than that Somebody Else’s Problem.

egwor 6y ago
Most ORM's I've used carefully choose just the columns required. i.e. they don't use 'select *'.
- jeppz 6y ago
  In some cases its not that simple, for example with Hibernate if you select specific columns then the object you get back won't be put in its L1 cache because its not the full object. So in some cases its better to select the whole object by primary key because some other method would do that later anyhow and now its in the cache and will skip an extra db call.
tester34 6y ago
weird. .NET ORMs don't do this (at least Entity Framework Core) `db.Users.Select(x => new { x.Name, x.Age }).ToListAsync()` will perform something like `SELECT Name, Age FROM db.Users` but ofc if you tell it to load everything `db.Users.ToListAsync()` then it will perform `SELECT Name, Age, Salary, ... FROM db.Users` but not *
- patates 6y ago
  That made me realize how much I missed .NET and my favorite tool LINQPad. Add Dapper next to your advanced ORM and you're basically unstoppable.
  - tester34 6y ago
    I do agree that combination of powerful ORM + light wrapper over raw SQL like Dapper makes stuff way easier (faster, less boilerplate-ish) while maintaining decent performance. People tend to say that: EF Core for saving data in db (because it detects changes and shortens code really hard) and Dapper for reads - writting manually good queries
- fabian2k 6y ago
  I was about to post the same thing. This works perfectly fine for EF Core, as long as you're aware that the mapping has to be directly in the Select like this so that it can be translated to SQL.
  - tester34 6y ago
    Exactly, you just have to check how's your query evaluation process doing if you want to do fancy stuff that SQL may struggle with / don't understand
amingilani 6y ago
If you're suggesting that there's room for improvement in ORMs, I agree. It'd be nice if the ORM figured out exactly what was going to happen and just fetch the data exactly required. Maybe that's something to work towards. If you're suggesting ORMs are bad because they make developer lives easier, I don't understand how one relates to the other.
- digiwano 6y ago
  I’m not saying developer-friendly APIs are bad, but i do think hand-crafted, well-written, and well-tested SQL outperforms any ORM, and if any ORM comes close to the performance of hand-crafted queries, it does so at the cost of complexity. Raw database queries aren’t difficult except in extreme edge cases that most ORMs aren’t smart enough to handle either. ORMs do make things slightly “easier”, but that comes at a cost. Whether that cost is in terms of performance or complexity or developers losing understanding/knowledge of how to build code that leans into the benefits of whichever database you choose comes down to whichever ORM you’re using, but that trade off will always be there. And either way, 99% of people using any random ORM have no idea whether a `select *` is being used or not. That’s the whole point. You put blind faith into whatever ORM believing it will do the “right”/“most optimized” thing.
  - fragile_frogs 6y ago
    On top of that you only have to learn SQL once and you can use it pretty much everywhere without having to learn a different ORM - Learn once, write everywhere. Another thing that speaks for raw SQL is that it's a lot easier to debug queries, just copy/paste the query into your SQL editor and start figuring out what's wrong, you can't just do that with an ORM.
    - tester34 6y ago
      "debug queries" yea, that's the point where things start getting exciting when you have logic in queries that you actually have to debug. I too love 200 LoC (tiny, in fact) procedures that inside build ""dynamic SQL"" aka string concat and EXEC with many OUTPUT parameters 10/10 experience, would recommend it to everyone. No, I don't want to debug my queries because it means that I'm probably doing too much on the database. I treat database more like a fancy data storage with outdated language, not as a business logic layer.
matthewmacleod 6y ago
But that’s the point of ORMs No, the point of ORMs is to map between relational databases and object-orientated programming environments, both of which are things which exist for good reasons. Your analysis is shallow and ill-informed – particularly given that many ORMs will very carefully select the columns which are selected in any particular query.
- digiwano 6y ago
  Knowing whether to `SELECT *` vs `SELECT a,b,c` is the entry-level/babys-first-optimization case. Providing a high level and performant ORM, takes deep knowledge of the underlying database and a lot of code complexity. But that wasn’t really my point. My point was: If you’re using an ORM this whole article is moot to you. You don’t decide whether `SELECT *` is the being used or not (let alone more complex optimizations), and if you do actually delve this deep into your ORM you are in the vast minority of coders if you’re using an ORM it’s an architectural decision that was mandated early on in whatever project, so even if you did find out a naive `select *` is being used by your third-party ORM, the whole point would be moot because we can’t just switch out ORMs for this project. So for most people using an ORM, this is useless information because either you don’t know/care or your organization won’t let you know/care because they’re already doing it that way organization-wide.
  - fisf 6y ago
    Any sane ORM will allow you to specify what data you want, and only select that. I'd argue that using an ORM as default and handcrafting optimized queries for specific edge cases should be the way to go.
goto11 6y ago
Use a better ORM. All ORM's are not equal. It is a broad class of frameworks designed for different purposes and with different trade-offs. My favorite is EF Core which allow you to select exactly the fields you want or do the equivalent to "select *". I'm sure there other ORM's which support the same.
- matwood 6y ago
  > Use a better ORM. This so many times. I also think before people talk about an ORM they need to define ORM as they can range from something like Hibernate to a simple object mapper. Is something like jOOQ and ORM where I can write typed SQL and it maps results to objects? I prefer writing SQL, but also prefer using a library that can do the tedious object mapping for me.

egwor 6y ago

Most ORM's I've used carefully choose just the columns required. i.e. they don't use 'select *'.

jeppz 6y ago
In some cases its not that simple, for example with Hibernate if you select specific columns then the object you get back won't be put in its L1 cache because its not the full object. So in some cases its better to select the whole object by primary key because some other method would do that later anyhow and now its in the cache and will skip an extra db call.

tester34 6y ago

weird. .NET ORMs don't do this (at least Entity Framework Core) `db.Users.Select(x => new { x.Name, x.Age }).ToListAsync()` will perform something like `SELECT Name, Age FROM db.Users` but ofc if you tell it to load everything `db.Users.ToListAsync()` then it will perform `SELECT Name, Age, Salary, ... FROM db.Users` but not *

patates 6y ago
That made me realize how much I missed .NET and my favorite tool LINQPad. Add Dapper next to your advanced ORM and you're basically unstoppable.
- tester34 6y ago
  I do agree that combination of powerful ORM + light wrapper over raw SQL like Dapper makes stuff way easier (faster, less boilerplate-ish) while maintaining decent performance. People tend to say that: EF Core for saving data in db (because it detects changes and shortens code really hard) and Dapper for reads - writting manually good queries
fabian2k 6y ago
I was about to post the same thing. This works perfectly fine for EF Core, as long as you're aware that the mapping has to be directly in the Select like this so that it can be translated to SQL.
- tester34 6y ago
  Exactly, you just have to check how's your query evaluation process doing if you want to do fancy stuff that SQL may struggle with / don't understand

amingilani 6y ago

If you're suggesting that there's room for improvement in ORMs, I agree. It'd be nice if the ORM figured out exactly what was going to happen and just fetch the data exactly required. Maybe that's something to work towards. If you're suggesting ORMs are bad because they make developer lives easier, I don't understand how one relates to the other.

digiwano 6y ago
I’m not saying developer-friendly APIs are bad, but i do think hand-crafted, well-written, and well-tested SQL outperforms any ORM, and if any ORM comes close to the performance of hand-crafted queries, it does so at the cost of complexity. Raw database queries aren’t difficult except in extreme edge cases that most ORMs aren’t smart enough to handle either. ORMs do make things slightly “easier”, but that comes at a cost. Whether that cost is in terms of performance or complexity or developers losing understanding/knowledge of how to build code that leans into the benefits of whichever database you choose comes down to whichever ORM you’re using, but that trade off will always be there. And either way, 99% of people using any random ORM have no idea whether a `select *` is being used or not. That’s the whole point. You put blind faith into whatever ORM believing it will do the “right”/“most optimized” thing.
- fragile_frogs 6y ago
  On top of that you only have to learn SQL once and you can use it pretty much everywhere without having to learn a different ORM - Learn once, write everywhere. Another thing that speaks for raw SQL is that it's a lot easier to debug queries, just copy/paste the query into your SQL editor and start figuring out what's wrong, you can't just do that with an ORM.
  - tester34 6y ago
    "debug queries" yea, that's the point where things start getting exciting when you have logic in queries that you actually have to debug. I too love 200 LoC (tiny, in fact) procedures that inside build ""dynamic SQL"" aka string concat and EXEC with many OUTPUT parameters 10/10 experience, would recommend it to everyone. No, I don't want to debug my queries because it means that I'm probably doing too much on the database. I treat database more like a fancy data storage with outdated language, not as a business logic layer.

matthewmacleod 6y ago

But that’s the point of ORMs No, the point of ORMs is to map between relational databases and object-orientated programming environments, both of which are things which exist for good reasons. Your analysis is shallow and ill-informed – particularly given that many ORMs will very carefully select the columns which are selected in any particular query.

digiwano 6y ago
Knowing whether to `SELECT *` vs `SELECT a,b,c` is the entry-level/babys-first-optimization case. Providing a high level and performant ORM, takes deep knowledge of the underlying database and a lot of code complexity. But that wasn’t really my point. My point was: If you’re using an ORM this whole article is moot to you. You don’t decide whether `SELECT *` is the being used or not (let alone more complex optimizations), and if you do actually delve this deep into your ORM you are in the vast minority of coders if you’re using an ORM it’s an architectural decision that was mandated early on in whatever project, so even if you did find out a naive `select *` is being used by your third-party ORM, the whole point would be moot because we can’t just switch out ORMs for this project. So for most people using an ORM, this is useless information because either you don’t know/care or your organization won’t let you know/care because they’re already doing it that way organization-wide.
- fisf 6y ago
  Any sane ORM will allow you to specify what data you want, and only select that. I'd argue that using an ORM as default and handcrafting optimized queries for specific edge cases should be the way to go.

goto11 6y ago

Use a better ORM. All ORM's are not equal. It is a broad class of frameworks designed for different purposes and with different trade-offs. My favorite is EF Core which allow you to select exactly the fields you want or do the equivalent to "select *". I'm sure there other ORM's which support the same.

matwood 6y ago
> Use a better ORM. This so many times. I also think before people talk about an ORM they need to define ORM as they can range from something like Hibernate to a simple object mapper. Is something like jOOQ and ORM where I can write typed SQL and it maps results to objects? I prefer writing SQL, but also prefer using a library that can do the tedious object mapping for me.