Comment by thomasmg | Hacker News Reader

Comment by thomasmg | Hacker News Reader

Parent Full thread

> regular programming languages are “lower level” in that regard and require you to do that optimisation yourself. The point of a language like SQL is that you describe the shape and content of what you want and don't have to worry about the most efficient way to retrieve that information.

Yes! But I wonder: wouldn't it be better if the developer has to specify how he wants to retrieve the information? In a regular programming language, typically people don't complain too much about this "missing feature" of a query optimizer. It's about modularization / abstraction. In SQL, the data model is king; in a regular programming language, the API is king. Yes, compilers have optimizers, but they optimize the machine instructions, not (so much) the algorithms that are used (scan vs using an index / using which index).

Maybe I just have seen too many cases where developers didn't create the necessary indexes, or databases did't use the (obviously) best indexes, or developers writing 10'000 lines SQL statements (that I then re-wrote in Visual Basic, and the resulting program was much faster). Or 1000 line GraphQL query that returns 50 MB of JSON. Yes, I have also seen Java developers writing exponential algorithms or worse, and running into memory issues - but I personally found it easier to analyze and optimize then SQL. Maybe I have some bias thought.

> Yes! But I wonder: wouldn't it be better if the developer has to specify how he wants to retrieve the information? In a regular programming language, typically people don't complain too much about this "missing feature" of a query optimizer.

On the other hand, as you noted, SQL is forever while programs and programming languages come and go. Maybe having a declarative data mindset is appropriate since data is more valuable and durable than code.

> > regular programming languages are “lower level” in that regard and require you to do that optimisation yourself.

> Yes! But I wonder: wouldn't it be better if the developer has to specify how he wants to retrieve the information?

I'd be against making SQL lower level in that sense – part of its purpose is to be back-end agnostic and once you start letting the dev get too details you are locking code to one implementation. Of course this isn't how things generally work anyway beyond the basics, with engine specific hints and such, but let us not intentionally make that more of an issue!

Though if we do go deliberately back-end specific, perhaps a way of describing a query plan more directly and saying “use this” is what you are wanting. Something like plan forcing in SQL Server's query store, but with you actually dictating the plan not picking one the engine has created from SQL. That way SQL stays purer but you have the option of something more direct, like mixing bits of (obviously platform specific) assembler into your C or other higher-level code. There will still be terrible code created that way though – possibly much worse.

> part of its purpose is to be back-end agnostic

Low-level languages can be backend agnostic. For example C or Rust allows to write low-level code that is back-end agnostic, and doesn't cause cause vendor or technology lock-in.

What I like to have is guarantees similar to what a RTOS (real-time operating system) has. Maybe using hints, or something like that. This could be standardized. Languages like C or Rust have such guarantees naturally: statements have some kind of guaranteed time constraints (that's why RTOSes can be mostly written in C). Sure, loops can be endless, but loops, e.g. in Rust, can't become 1 million times slower suddenly because the backend changed.

> But I wonder: wouldn't it be better if the developer has to specify how he wants to retrieve the information? In a regular programming language, typically people don't complain too much about this "missing feature" of a query optimizer.

Why not use a key–value store or just a file system, if you want to write everything yourself?

I don't want to write everything myself. (Well, I did write 3 relational database engines, and 4 query optimizers... Hypersonic SQL, PointBase Micro, H2 database, and the Apache Jackrabbit Oak query engine.)

I want a good abstraction (API) that allows to write programs that access and manipulate data in an efficient and simple way. But I argue it shouldn't be a declarative set manipulation language like SQL, but a language that is more low-level and makes it hard to (by mistake) retrieve or update millions of rows, or makes it hard to write statements that are very inefficient, and makes it hard to have systems that lack encapsulation, so you end up having hundreds of tables that are entangled in a big mess.

I do like the fact that network roundtrips can be reduced: you can send SQL (or GraphQL) statements from the client to the server, where the statements are processed and the result is returned. But it's too easy for the result to become large, or statements too slow to be processed. With a key-value store (like Redis) you can't do that. Or can you send a list of commands to Redis (or some other key-value store) that are executed in a safe and efficient way (with guaranteed runtime) and the result is returned? That would be what I look for.

Reply on news.ycombinator.com