4 ms·

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 synt

by 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.
jmull 6y ago
There are a lot of ways to write a SQL query against a set of tables, given a set of input parameters, to get the desired result. (Not just syntactic or other superficial differences, but differences in logic and how relationships are specified.) And, of course, an optimizer will handle different SQL queries differently.