5 ms·

I don't agree with this conclusion. I've worked with Postgres, MySQL and Oracle, and found that it is important to have a good understanding of the possible exe

I don't agree with this conclusion. I've worked with Postgres, MySQL and Oracle, and found that it is important to have a good understanding of the possible execution plans for a query. I will sometimes construct a query very carefully to achieve a particular plan. And when things go wrong, I run EXPLAIN PLAN, examine statistics, etc., and tweak my query to do what I want. You really have to do that to obtain good performance in some cases. It does undercut the claim that SQL has to being non-procedural, but that's life. I do NOT want to have to construct the query execution plan manually for every single query. Usually, the optimizer will do a fine job, assuming the database designer has made good choices regarding indexes and other physical design issues. But there are always a few complex, tricky queries where you do have to understand internals. The "cognitive load" is the same, and having a high-level query language means that when you do make your subtle and deft change to rescue performance, you often just tweak a query, instead of rewriting a detailed query plan. (Extending this position a little: I have come around 180 degrees and consider ORMs to be a bad idea overall. When you do need to be very careful about writing a query, the ORM adds a layer of complexity -- not only do you need to control the SQL, but you now need to ensure that your ORM can produce that SQL.)

JoshTriplett 11y ago
In theory, if you had an explicit query language, you could write the query planner in that language. You could then rely on the default query plan by calling functions invoking it, or make part or all of that plan explicit by invoking the lower-level functions directly.
icebraining 11y ago
When you do need to be very careful about writing a query, the ORM adds a layer of complexity -- not only do you need to control the SQL, but you now need to ensure that your ORM can produce that SQL A good ORM will let you run manually written SQL queries and map the results to objects. For example in Django: https://docs.djangoproject.com/en/1.8/topics/db/sql/ https://docs.djangoproject.com/en/1.8/topics/db/sql/
- lucian1900 11y ago
  Or even better, an ORM can be layered on top of an SQL abstraction that maps directly to what the database understands: http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html It then becomes easy to go one level below the ORM without giving up all the nice things.
- geophile 11y ago
  Right, so an ORM makes the easy things easier. The hard things are either harder or you need the escape hatch. I've both created an ORM (long ago, back in Java 1.0 days) and used a few. My conclusion is that the real pain in interacting with a database isn't writing SQL, it's managing the API (e.g. JDBC). Managing PreparedStatements, managing ResultSets, handling exceptions, getting rid of warnings, mapping to objects (if that's what you want), extracting each field and converting appropriately, binding parameters (again, with appropriate conversions). I eventually decided that my ideal ORM just managed all that crap and let me write the SQL. (Of course, it's only approximately 0.5 of an ORM at that point, but I don't care if it no longer deserves the title.) It did the book-keeping, and let me focus on 1) the application (written in Java), and 2) the SQL. I then discovered that iBatis is based on this idea, (I think the name is slightly different now).
  - kibibu 11y ago
    > Right, so an ORM makes the easy things easier It certainly makes migrating from MySQL to Postgres a lot easier.
  - icebraining 11y ago
    I guess it depends on your needs. On the platform I work on, the user can configure a set of security restrictions on tables (e.g. users of group 1 can only read records of table X which have public = TRUE; users of group 2 can only update records of table Y which have user_id = [their own user id]), and the ORM automatically inserts those restrictions into any query made to those tables. Doing this with manually written queries would be, if nothing else, a PITA. Then again, it's a specific need; I suppose most applications have static, not dynamic rules.
jameshart 11y ago
The real trouble comes when your carefully manually tuned version of the query which works optimally for the data and loading and database version you have on date X, is still running on date X + 5 years. When the DB engine has upgraded its optimizer, and the data patterns have changed, and the usage levels are very different. And the original, unoptimized, declarative query would now be able to run faster.
- aidos 11y ago
  But you're not in any worse position than those who did it all manually in the first place. In an ideal world the query planner would take care of everything and as the shape of the data changes it could create the appropriate optimal queries. Doing that manually is just wasteful you'd spend all day tweaking queries that are now running suboptimally because of data changes. It would be pretty upsetting to have to change all your rethink queries to use a new index you didn't put in initially. I think this article is just making excuses for rethink not having a query planner/optimizer and my guess is that one day they'll end up having to create one. I guess Postgres didn't have one once upon a time either and now it's incredibly sophisticated.
- trhway 11y ago
  separation of concerns - a declarative query and the plan to execute it are 2 different artifacts. Thus DBA can upload new plans or regenerate plans for the same declarative query when things change like you described. If your database doesn't support it - well, choose your database carefully :)

JoshTriplett 11y ago

In theory, if you had an explicit query language, you could write the query planner in that language. You could then rely on the default query plan by calling functions invoking it, or make part or all of that plan explicit by invoking the lower-level functions directly.

icebraining 11y ago

When you do need to be very careful about writing a query, the ORM adds a layer of complexity -- not only do you need to control the SQL, but you now need to ensure that your ORM can produce that SQL A good ORM will let you run manually written SQL queries and map the results to objects. For example in Django: https://docs.djangoproject.com/en/1.8/topics/db/sql/ https://docs.djangoproject.com/en/1.8/topics/db/sql/

lucian1900 11y ago
Or even better, an ORM can be layered on top of an SQL abstraction that maps directly to what the database understands: http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html It then becomes easy to go one level below the ORM without giving up all the nice things.
geophile 11y ago
Right, so an ORM makes the easy things easier. The hard things are either harder or you need the escape hatch. I've both created an ORM (long ago, back in Java 1.0 days) and used a few. My conclusion is that the real pain in interacting with a database isn't writing SQL, it's managing the API (e.g. JDBC). Managing PreparedStatements, managing ResultSets, handling exceptions, getting rid of warnings, mapping to objects (if that's what you want), extracting each field and converting appropriately, binding parameters (again, with appropriate conversions). I eventually decided that my ideal ORM just managed all that crap and let me write the SQL. (Of course, it's only approximately 0.5 of an ORM at that point, but I don't care if it no longer deserves the title.) It did the book-keeping, and let me focus on 1) the application (written in Java), and 2) the SQL. I then discovered that iBatis is based on this idea, (I think the name is slightly different now).
- kibibu 11y ago
  > Right, so an ORM makes the easy things easier It certainly makes migrating from MySQL to Postgres a lot easier.
- icebraining 11y ago
  I guess it depends on your needs. On the platform I work on, the user can configure a set of security restrictions on tables (e.g. users of group 1 can only read records of table X which have public = TRUE; users of group 2 can only update records of table Y which have user_id = [their own user id]), and the ORM automatically inserts those restrictions into any query made to those tables. Doing this with manually written queries would be, if nothing else, a PITA. Then again, it's a specific need; I suppose most applications have static, not dynamic rules.

jameshart 11y ago

The real trouble comes when your carefully manually tuned version of the query which works optimally for the data and loading and database version you have on date X, is still running on date X + 5 years. When the DB engine has upgraded its optimizer, and the data patterns have changed, and the usage levels are very different. And the original, unoptimized, declarative query would now be able to run faster.

aidos 11y ago
But you're not in any worse position than those who did it all manually in the first place. In an ideal world the query planner would take care of everything and as the shape of the data changes it could create the appropriate optimal queries. Doing that manually is just wasteful you'd spend all day tweaking queries that are now running suboptimally because of data changes. It would be pretty upsetting to have to change all your rethink queries to use a new index you didn't put in initially. I think this article is just making excuses for rethink not having a query planner/optimizer and my guess is that one day they'll end up having to create one. I guess Postgres didn't have one once upon a time either and now it's incredibly sophisticated.
trhway 11y ago
separation of concerns - a declarative query and the plan to execute it are 2 different artifacts. Thus DBA can upload new plans or regenerate plans for the same declarative query when things change like you described. If your database doesn't support it - well, choose your database carefully :)