4 ms·

I don't see the conflict? If the GraphQL query is translated into SQL on the server, then then the query optimizer would optimize that just as effectively as if

• mixedCase 6y ago
  > then the query optimizer would optimize that just as effectively as if the query had been written in SQL originally ...and other lies we tell ourselves to sleep soundly at night. But just like ORMs, they do work for the simple cases which tend to abound and you can hand-optimize the rest.
  • goto11 6y ago
    > ...and other lies we tell ourselves to sleep soundly at night. So tell me how the query optimizer could possibly tell the difference between generated and hand-written SQL?
    • mixedCase 6y ago
      Because the SQL will not be the same. In complex cases, SQL generated by an ORM will do a combination of "more than it needs to" and plain inefficient queries. It can only do so much with what it knows about the database and the API used to call it. Hand-written SQL gives the author a chance to be more precise in its needs, not only in the SQL but also by pre-generating obvious indexes to be performant from the get-go.
      • goto11 6y ago
        Appropriate indexes will be utilized (by the query planner) whether the SQL is hand-generated or generated from some other query languages. It doesn't make a difference. How could it? And the whole point of GraphQL is that you specify exactly what data you need, so overfetching is avoided. This is in contrast to traditional REST API's where you get a fixed resource.
        • mixedCase 6y ago
          > Appropriate indexes will be utilized If present. And for that they need to be created, and which are the right ones is less obvious when working higher in the abstraction staircase. > It doesn't make a difference. How could it? Because the SQL generated by ORMs can be wildly stupid in many cases. Here's one blogpost with an example, where regular SQL and query builder that maps to SQL almost directly generate a decent query on a simple relation, while a full ORM does something stupid: https://blog.logrocket.com/why-you-should-avoid-orms-with-examples-in-node-js-e0baab73fa5/ https://blog.logrocket.com/why-you-should-avoid-orms-with-ex...
          • goto11 6y ago
            The article shows one query generator which generates SQL which are logically equivalent to the hand written SQL, and compares it to another tool which generate inefficient queries. So it is not an inherent problem with generated SQL, just with some particular tool. So...use the right tool? GraphQL allows you to specify what data you need. Obviously, if you use some middleware which throws this information away and just fetches everything from the database, then you have an inefficient system. But this is not a problem inherent to GraphQL or generated SQL in general.
• jmull 6y ago
  The SQL your graphql implementation’s ORM middleware generates won’t optimize as well as hand-written SQL in many cases. A decent system will provide the hooks you need to hand optimize certain cases somehow, but There are always limitations and hoops to jump through and additional complexity to manage. The extra layers that are meant to make your life easier are getting in the way instead. (May or may not still be worth it, but the point is, it’s not a foregone conclusion.)
  • goto11 6y ago
    But why wont it optimize as well as hand-written SQL? How can the query optimizer even tell the difference? Surely the SQL is transformed into some kind of syntax-independent abstract query tree before it is passed to the optimizer.
    • mixedCase 6y ago
      No such thing. The database interface is SQL. The query optimizer is something run in the database.
      • goto11 6y ago
        Yes I mean inside the database engine. The engine receives SQL and parses it and then pass the query tree to the optimizer which generate a query plan. My question is how the optimizer could have problems if the SQL is generated as opposed to hand-written? How could that make a difference?
        • mixedCase 6y ago
          As mentioned in another the comment, because SQL that is hand-written for non-trivial cases will too often be better than what an ORM generates.
          • goto11 6y ago
            This is so hand-wavy I can't really argue against it.