12 ms·

Give me an O! Give me an R! Give me an M! What does that spell? SLOW PERFORMANCE! Todays programmers dont understand data. They understand frameworks. To find

• sfvisser 8y ago
  People do this because it’s easier, takes less time, and is within the paradigm their head is already in all day. We should fix our abstractions instead of telling people they don’t understand data. The latter is fine every so often, but doesn’t scale and isn’t even true most of the time.
  • ianamartin 8y ago
    Or maybe we should just nut up and tell people they don’t understand data?
    • girvo 8y ago
      Why not both? Truly, we should work to have better abstractions. We shouldn’t glorify making our own lives more difficult. And we shouldn’t glorify ignorance either! It’s not an either/or dichotomy here
• patkai 8y ago
  I love to write SQL queries and even use them extensively in my code. However, almost everyone I talk to resists this, and warns me that "one day you will regret..." It gives me an uneasy feeling that I actually might, though it hasn't happened yet.
  • idle_zealot 8y ago
    So long as you're using prepared statements, you should be okay.
    • weavie 8y ago
      What difference does using prepared statements make?
      • skunkworker 8y ago
        You can avoid SQL injection. It makes it harder to shoot yourself in the foot, though still possible to inject if your prepared statement includes a string interpolated variable.
        • weavie 8y ago
          Yes, good point.
      • qaq 8y ago
        You have abstraction between your code and DB you can change underlying tables but interface will remain consistent requiring no changes to the application. There are security benefits too easier to prevent SQL Injection, you also only give the app user permission to execute sps so no arbitrary queries can be run.
  • reacweb 8y ago
    Writing better code makes programs more robust and reduces maintenance cost. For a management point of view, the boss may think that your project was more easy than the project given to the other team that butchered their work.
  • weavie 8y ago
    I think one of the problems with SQL queries is that as far as your application is concerned they are just strings. There's no typing information or even syntax checking. Also if you do something like 'select * from' then there the results returned aren't deterministic. If you scatter these throughout your code and then the database schema changes, you have a hell of a refactoring job to make sure everything still works. One advantage of an Orm is that if you keep your objects in line with your database the generated sql will stay correct. Personally, I am more than happy to take that hit. I think it is a price well worth paying in order to have optimised queries that do exactly what I what them to do. To make it easier for myself though I will always try to keep my queries in one place in the code. Then all my code needs to know is it is getting clients_older_than(32) or whatever..
• fuzzy2 8y ago
  An ORM doesn’t have to be slow. Most can source their data from custom queries or stored procedures. Not using an ORM requires writing lots of code to do what the ORM would otherwise do. Is that really appropriate today? Should we not take advantage of the available CPU/RAM? I say ORM is the correct choice in many cases. Not always.
  • blowski 8y ago
    ORM doesn’t have to be slow, but I have seen a lot of implementations like the parent comment. For example, doing a foreach over 2000 rows where an UPDATE would be much, much quicker. ORMs help a lot with developing software, but you need to profile and optimise - in some cases bypassing the ORM.
  • TekMol 8y ago
    I say ORM is the correct choice in many cases I'm still undecided about this. Would be fun to compare some actual approaches. Which is your favorite ORM?
    • fuzzy2 8y ago
      Currently I’m trying to use Dapper if possible. It allows me to write efficient queries manually and just maps the results to objects. For simple queries though (single-table), Entity Framework is just as fast. Because I’m mostly working on dashboards and stuff, writing to the database isn’t much of a concern.
      • TekMol 8y ago
        What is the code to get the number of cars with expired insurence when using Dapper?
        • fuzzy2 8y ago
          Something along the lines of db.Query<Car>("SELECT * FROM Cars WHERE InsuranceEndDate < @Date", new { Date = DateTime.Now }); /edit: Sorry, missed the "number of". Well, you get the idea. It'd use 'QuerySingle' instead.
• manicdee 8y ago
  That’s how I have seen it done in Perl, PHP, Django, etc as well. It’s not an ORM thing it is a naive programmer thing. The ORM makes it easier to do things like Class.filter(insurance_end_date < today_date) But hey what do I know, I am not berating the youf of today so I don’t belong in this skit.
  • TekMol 8y ago
    Class.filter(insurance_end_date < today_date) Which ORM is that? It would be cool to compare some approaches to common problems.
    • blowski 8y ago
      Looks similar to Laravel’s Eloquent, which is inspired by Rails.
    • irishsultan 8y ago
      Diesel has something similar (except that it can't overload < (you can overload < in Rust, but the type doesn't match what's needed), so you need to specify it as insurance_end_date.lt(today_date))
    • brobdingnagians 8y ago
      C++ has a similar thing with sqlpp11: https://github.com/rbock/sqlpp11 https://github.com/rbock/sqlpp11
    • funkydata 8y ago
      The Django ORM for example. You can ever traverse relations and still use the same syntax: https://docs.djangoproject.com/en/2.0/topics/db/queries/#lookups-that-span-relationships https://docs.djangoproject.com/en/2.0/topics/db/queries/#loo...
    • manicdee 8y ago
      ASP.NET MVC4 also has Linq so you can do stuff like Class.All(x => x.attribute == value).ForEach(foo => foo.method()) Django has similar functionality through Q and F expressions. Most ORMs have this functionality of specific language constructs to allow complex queries to be expressed without writing SQL, just with slightly different semantics.
• dagw 8y ago
  That has nothing to do with ORMs. I've seen plenty of people write code like: SELECT * FROM DATA for each r in result if r.x > 12 do_something(r.y) Exactly the same thing without any ORM. If anything ORMs should improve performance for novices since it makes it a lot easier for people to writer better queries that run on the database.
  • TekMol 8y ago
    The ORM makes it more likely to do stuff in the application instead of the DB because many queries are hard or impossible to represent in an ORM fashion. Additionally, the ORM makes it much harder to see what is going on under the hood. For example you don't know if a value of an object is stored in the main table or lazy loaded from a related table. So you have no clue if echo "$user.name lives in $user.city" results in 0 sql queries or one or two.
    • threeseed 8y ago
      > ORM makes it much harder to see what is going on under the hood I've never in the last 20+ years seen an ORM that doesn't allow you to log the SQL queries with a single configuration option.
      • ben-schaaf 8y ago
        Heck, I'm disappointed in any ORM that doesn't log queries by default in development mode!
      • asfdsfggtfd 8y ago
        With an add on Django even displays them in the browser!
        • paulie_a 8y ago
          It's trivial to find optimizations in django debug toolbar..."oh yeah I should use select_related" If you have no idea what you are doing with an orm you probably won't be able to write decent SQL anyways.
• grey-area 8y ago
  I use an ORM. I don't perform queries like that because the ORM makes it easy to build complex queries, joins, limiting the data returned etc, then execute them in one query, it's a convenience, not a straightjacket. The problem is not ORMs, the problem is just people not thinking about the resources their query over some data takes, and trying to do things in memory that are better done in the database (as in your example), because databases are hard, and sql is not a particularly friendly language, so they take the easy way out. They'd do things like iterating over all instances just as much if not using a framework/ORM, because they understand their programming language and don't care to understand SQL, and also because they get away with it when there are 1000 cars in the fleet, and didn't anticipate having 1 million.
  • strken 8y ago
    One of the biggest problems with ActiveRecord (the ORM in the article) is that it uses extremely obtuse names for extremely common methods. There are three different methods for finding how many things meet a given criteria[0], and none of them do the same thing. There are three different methods for loading data from an associated table[1], and none of them do the same thing either. None of the method names explain how the function is implemented or whether it's going to hit the database. This is a problem that goes beyond the usual ORM antipatterns and bad database designs and becomes its own special kind of hell, because it makes code far harder to reason about and thus far harder to review, in a way that I don't remember encountering in Hibernate, Gorm, or anything else. [0] count, length, and size [1] includes, preload, and eager_load
    • grey-area 8y ago
      Yes there are a few problems with ActiveRecord; it has been well used and has accreted lots of similar functions (a victim of its own success), and also it doesn't make it crystal clear when you are hitting the database. IMO ORMs should be simple, unsurprising, and only have one or two methods which actually execute the sql and fetch data - they are there to make it simpler to build queries and map rows to objects, not to blur the line between fetching and manipulating data.
    • 8y ago
• manigandham 8y ago
  Not this again. ORMs are tools, basically dynamic code generators that run SQL and map the results to in-memory objects, and vice-versa. Some are simplistic and others are incredibly advanced, and the code itself is usually faster than your own sql->objects logic that you would write otherwise. The issues with performance are almost always with the way the tool is used, like choosing a bad algorithms or the wrong data structure for a certain situation. Lazy-loading a large child object in a tight loop over 100s of entries will always be slow because it's an improper approach, and nothing other than knowledge and competency will resolve that. It's rather amazing ORMs can be so controversial when the real issue is the developer, which speaks to the far bigger problem of expertise and quality in this industry.
  • l0b0 8y ago
    Not this again. ORMs are some of the most impenetrable code you'll ever see. Understanding the performance characteristics of a CRUD application using SQL is vastly simpler than trying to puzzle out the arcane ways in which ORMs decide to throw up garbage once you add one more thing to the mix.
    • EGreg 8y ago
      Not that again. ORMs achieve one main thing: being able to map your app’s objects to a relational database and back. But there are tons of other benefits: 1) Avoid all injection attacks by default by binding variables rather than interpolating their vakues 2) Write SQL code for you to automatically, so you always have balanced parentheses and no typos or errors mixing statements 3) Autogenerate classes and methods from the SQL model automatically so there is a place where you can add custom methods on objects 4) Model fields and relationships in a way the app can understand, so you can make meaningful error checks and manipulations in the app instead of relying on the database engine to give you a nice error message or manually copying the data type logic. 5) Play nice with version control, making sure to encapsulate the code in ONE PLACE instead of a million places when the schema or model changes. 6) Pissing people off on HN so we can better discuss and explain principles of architecting good softeare while talking anout the benefits of ORM 7) Let you write an adapter to move to eg a graph database which is far faster. 8) In fact, just making you avoid doing joins in the database by default is already a feature as you can make your app far more scalable w sharding and possibly think about making it byzantine fault tolerant and distributed! See for example this: https://qbix.com/platform/guide/database https://qbix.com/platform/guide/database https://qbix.com/platform/guide/models https://qbix.com/platform/guide/models
• mpweiher 8y ago
  That's one thing I love about Gemstone/Smalltalk. In Smalltalk, you would write such a query as: (cars select: [ :car | car insurance endDate < now ]) size. (There probably is a shortcut for just counting the elements, though admittedly you have to have each car for the query). So what Gemstone does is that when cars is a DB collection, it interprets the block and turns it into a query. So the code for iterating in-memory and the code for doing an optimized query is the same.
  • vidarh 8y ago
    In Ruby, using Sequel: DB[:cars].where { insurance_end_date < Time.now }.count (replacing "Time.now" with Sequel.function("NOW") if you want server side, or whatever date/time formatting function you want that'll return time/date in your desired format client side; client side it'll be evaluated once) I think most ORMs can do a reasonable job at this in languages where you have sufficient flexibility in overloading behaviour.
• Jach 8y ago
  This thread demonstrates that ORM is still (one of several of?) our field's Vietnam war... http://blogs.tedneward.com/post/the-vietnam-of-computer-science/ http://blogs.tedneward.com/post/the-vietnam-of-computer-scie... On top of the data/frameworks issue there's the one of data locality. Frameworks make the tradeoff decisions for you, but not always the right ones since the right tradeoffs are application-dependent. In one context using a cluster of commodity machines that do a mix of compute for your service and store and shuffle data around is a good idea. In another context a few beefy servers with the beef divided up between the app servers and database servers depending on where most of the compute most efficiently takes place is a better idea. (Though even with poor performance design, hardware is still good enough that a lot of things run just fine by having multiple services on one modest machine. That doesn't stop lots of programmers from reaching for frameworks that demand easily scaleable multi-machine systems even when there's no need and won't ever be need.) In any case consistency matters: SQL strings are just one form of shipping code to the data to compute remotely instead of retrieving the data and computing locally. When you bring in an ORM, inconsistency is bound to follow with lots of computation that could have been in a query or stored procedure now living in the ORM framework and the application itself. I can at least understand many people's annoyance with stored procedures though. Most DBs procedural extension languages suck badly enough that shipping JavaScript strings over to NoSQL stores can seem like a big improvement, and various levels of SQL standard conformance is all you typically get to use from the broader design pool of declarative languages.
• asfdsfggtfd 8y ago
  All the ORMs I have used can easily do the equivalent of this select count(*) from cars where condition; If programmers are doing what you say that they are then the programmers are the problem not the library. Furthermore most ORMs (certainly any that I would consider using!) allow escaped SQL to be used - and if the query gets much more complicated than a couple of where clauses I consider using this feature. A decent ORM used well allows programmers to program faster on the simple stuff but still write fast code for the complex stuff.
• lc94 8y ago
  What a stupid comment. Your point has absolutely nothing to do with ORMs and only shows your utter inexperience with them. You could replace everything you said with using SQL directly and just doing a select * from cars
  • dang 8y ago
    This comment breaks the site guideline against calling names in arguments. Could you please read https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html and stick to the rules, regardless of how wrong or annoying another comment might be?
• slifin 8y ago
  ORMs are terrible but you can make the same mistakes in the article without a ORM, or in a query builder I think the article alludes to much more important problems like querying in a loop, querying the same information again just because it's not in function/object scope A lot of the mistakes I see are because developers don't learn SQL, use it wrong, then assume it's slow, then use NoSQL, then reenforce each other into believing NoSQL is saving their performance Let's not make this a conversation on ORM or not, but how do we make sure developers understand how to properly make use of their relational databases, which for the last decade or so has been painted as old & cruddy compared to sexy NoSQL
• dnomad 8y ago
  The problem isn't "kids these days." The problem is relational databases. It's a profoundly non-intuitive and fundamentally broken model. Developers shudder away from SQL precisely because it is so alien from the problem they're trying to solve. As for performance, note that even when you know exactly what you're doing it is hard to wring good performance out of relational databases. This is why companies spend hundreds of thousands of dollars a year to hire "Database Administrators" to design and "tune" their database. (What other piece of software requires a highly paid, full time expert?) The most complicated databases are so complicated that we get "Junior DBAs" and "Senior DBAs" and multiple levels of certification. And let's not forget what happens at extreme scales. At the lowest latencies and the largest data sets relational dbs are simply impossible to use. This might never be a problem for most businesses who are processing a few hundred messages a second (if that) but it should be on the mind of any startup that hopes to one day have millions of customers. In the long run memory will become cheaper, faster and persistent. (Let us pray.) When that happens most everybody will abandon the big mess that are relational databases and just manipulate objects in memory as the gods intended. Then the relational model just becomes something to scare grand kids with.
• jrs95 8y ago
  Anyone who does this is just bad. I’d be surprised if they could do SQL without an ORM either. I really have to question the value of a CS degree when there are all of these people who have them and still don’t seem to realize that running database queries in a loop is a bad idea. And they’ll still get hired because they’ve successfully memorized some data structure trivia that they’ll almost certainly never use.
• Ntrails 8y ago
  Interesting. I have had a couple of people in interviews (who know SQL/Databases on cv) writing code a lot like that pulling in two whole tables, joining them, and then filtering the joined results. It is possible i was overly harsh in my judgement, as assumed meant no real familiarity at all.
• pc86 8y ago
  This isn't what happens when you use an ORM, this is what happens when you have a JavaScript code monkey writing data access code and then slapping the word "Enterprise" on it. The places I've worked at that have written true Enterprise software, whether consulting, publicly traded healthcare companies, banks etc, have had extremely knowledgeable DBAs writing stored procedures for data access. The ORM usage was reserved for modifying the results of stored procedures or small things like account management. Nothing truly critical to the business was handled through an ORM directly pulling data from tables.
• littlestymaar 8y ago
  “Todays programmers”: I'm sorry to disappoint you, but this isn't new. My father (DB engineer, now on the verge of retirement) has been complaining about that for almost two decades now! Just as a reminder, Hibernate is 17 years old :). Also, modern ORM frameworks give ways to iterate on the data from the programmers language, but translated directly to SQL. See what [Diesel](http://diesel.rs/ http://diesel.rs/) does, for instance. The paper talks about other issues, which comes from negligence or a lack of understanding of the performance costs of the queries. But this performance impact could aslo happen in a SQL-only environment (not using a framework won't help you to create indexes, or to add pagination to your queries).