Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why your query language should be explicit

blog.hiphipjorge.com

Post reply on HN
Print view View original

31–40 of 47 posts

Re: Why your query language should be explicit

#31

Post by geophile »

icebraining wrote: post #13
geophile wrote: post #5

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 pe…

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/

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).

Re: Why your query language should be explicit

#32

Post by arundelo »

orf wrote: post #25

I hate to criticize but this stood out to me: > SELECT * FROM users WHERE name = 'jorge' ORDER BY age; > Can we tell from the query if this is the case? No, we can't. You'd have to look it up. and then: > r.table('users').filter({ name: 'jorge' }).orderBy(r.desc('age')) > Now, can you tell from the query if the users are filtered or ordered first? Yes! filter comes first. The filter comes first in both queries. It's…

The author's point here is that in ReQL the order things happen in is the order you write them in, which is not the case in SQL. (In SQL the order things happen in is partially baked into the language -- see the page that "look it up" links to in the article -- and partially at the discretion of the query plan optimizer.)

Re: Why your query language should be explicit

#33

Post by kibibu »

geophile wrote: post #31
icebraining wrote: post #13

Earlier quoted context omitted.

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/

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 object…

> Right, so an ORM makes the easy things easier

It certainly makes migrating from MySQL to Postgres a lot easier.

Re: Why your query language should be explicit

#34

Post by ngzhian »

hobs wrote: post #29
cwyers wrote: post #21

> In this query, we get all the users with the name 'jorge' are queried and then ordered in descending order by age. > SELECT * FROM users WHERE name = 'jorge' ORDER BY age; > If we wanted to dig deeper into this query, we might want to know if the "WHERE" is getting executed before the "ORDER BY". Can we tell from the query if this is the case? No, we can't. You'd have to look it up. No I wouldn't. I can look right…

In terms of order, it's not clear that the order by happens "after" the where clause, for instance, what if the results are pre-ordered? If the index used guarantees an order, and that order is the same as your order by, it is redundant and no ordering will need to occur. However, I agree with the thrust of your statement, and if you cant figure out SQL's query plans, writing your own from scratch may be a tall order…

The SQL version reads left-to-right, similar to how ReQL's version reads top to bottom, and both reflects the execution order (in terms of a common way of text flow). From my understanding, ReQL does not do any form of optimization based on the complete query, the `.()` punctuates the flow of command. Whereas and SQL statement is just one compound thing made up of different commands where the SQL engine can optimize, which makes it hard to tell.

Re: Why your query language should be explicit

#35

Post by TheLoneWolfling »

What I want is a language with feedback.

In other words, a language that calculates all of the various optimizations behind-the-scenes, and sees what ones it thinks would be good and suggests them to you. And you can add annotations to allow it to do specific optimizations.

It has control and transparency, but keeps it relatively easy to optimize. And you can hide the annotations if you really wish.

Re: Why your query language should be explicit

#36

Post by mnarayan01 »

As others have already mentioned, what the author is calling "explicit" would be more typically called "imperative". I'm going to go further though and say that I think "explicit" as used here is actually wrong. Take the example from the article:

  SELECT * FROM users WHERE name = 'jorge' ORDER BY age;
and assume that we have exactly one index on the table, namely a compound one on `name, age`. If that index is used, then _only_ the filtering need be done. In SQL, because the execution sequence is left unspecified, we can continue to write the query as is while still allowing the DB to skip the unneeded ordering step (whether it does so or not is a different question obviously).

If, however, the execution sequence must be "specified" (cf. "explicit") and you don't want to perform the unnecessary ordering step, then either the order must be left out of the query (and thus implicit), or the DB needs to be able to ignore what you tell it to do.

Re: Why your query language should be explicit

#38

Post by pacala »

cwyers wrote: post #21

> In this query, we get all the users with the name 'jorge' are queried and then ordered in descending order by age. > SELECT * FROM users WHERE name = 'jorge' ORDER BY age; > If we wanted to dig deeper into this query, we might want to know if the "WHERE" is getting executed before the "ORDER BY". Can we tell from the query if this is the case? No, we can't. You'd have to look it up. No I wouldn't. I can look right…

Every so often the optimizer does it wrong. Instead of reading query plans [new language] and adding a layer of hints [yet another language], how about having a language that allows you to specify the precise order when you eventually care, and optimizes anyway by default. As opposed to three languages layered over each other in a fairly ad-hoc way.

    val q = Q.from(table).select(foo).filter(foo > 3)
    val q = Q.from(table).select(foo).filter(foo > 3).verbatim()
PS. Also, the query plan language is now the same as the query specification language.

Re: Why your query language should be explicit

#39

Post by friendzis »

cwyers wrote: post #21

> In this query, we get all the users with the name 'jorge' are queried and then ordered in descending order by age. > SELECT * FROM users WHERE name = 'jorge' ORDER BY age; > If we wanted to dig deeper into this query, we might want to know if the "WHERE" is getting executed before the "ORDER BY". Can we tell from the query if this is the case? No, we can't. You'd have to look it up. No I wouldn't. I can look right…

Ability to shoot yourself in the foot IS a feature (or inevitability, depends on how you look at it). Requirement to shoot between feet is not, though.

The problem here is that SQL is declarative and there is nowhere to dig deeper - data set is described. That's what SQL does - describes data sets. And any attempt to dig deeper will lead to imperative code.

With SQL I would actually hope that I cannot (statically) look up execution order without taking indexes, partitions and what-not into account.

Re: Why your query language should be explicit

#40

Post by kragen »

Jorge writes:

> Does this increase cognitive load? Yes, it does. But this is outweighed by the ability to understand how your query is being executed. … Hence, when you see a query you immediately know that it's using an index…

This reminds me of something I read in a paper once:

> Accordingly, it provides a basis for a high level data language which will yield maximal independence between programs on the one hand and machine representation and organization of data on the other.

It seems like this Jorge dude is claiming that it's great that, if you use his company's product, you have to change your program when you change the representation and organization of data on your disk, and that there are no real disadvantages to this. I think maybe he should read the paper I'm quoting from above, which is Codd 1970, introducing the relational database: https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf — Codd explains why the 1960s CODASYL systems similar to RethinkDB made programs unmaintainable.

If you don’t understand why relational databases got adopted in the first place, you aren’t qualified to “rethink databases” or to call yourself a “full-stack developer”. And your gullible customers, although they may get a prototype built quickly, will be outcompeted by their rivals who aren’t afraid of using query optimizers. Jorge must think we're all fucking idiots who don't know why we abandoned products like RethinkDB thirty or forty years ago.

Fortunately, the HN thread is much more intelligent and informed than the original article!

Post reply on HN