Hacker News Reader

Comment by ljw1001 | Hacker News Reader

You've demonstrated above that using RDBMS and SQL involves making tradeoffs, like everything else. But you probably can't imagine a world without relational databases. I know I can't.

Many of the problems you mention above occur because the database handles stuff for programmers. Sure. you could create a custom solution around your biggest bottlenecks, but do you want to create a custom solution for every query, or do you want the database to do it for you. The generation of SQL admins is a replacement for a much larger group of programmers that would be needed if they weren't here, and more importantly, an army of people to deal with security, reliability, etc. that people using a good RDBMS get to take for granted.

View on HN

I'm saying there could be another high level language that took the place of SQL. An imperative language with explicit algorithmic declarations. I'm not talking about custom solutions, I'm talking about how the web has evolved away from ideas that could have been better, because SQL to me is clearly not what I imagine to be the best we could possibly do for database queries. Same story with javascript and CSS.

which to you would be more clear?

SELECT * FROM TABLE WHERE id = 56

binary_search(column_name=id, value=56, show_all_columns=True)

If deciding join order and what indexes to use sounds like your idea of a good time. It certainly isn't mine.

parameters could have default values or default heuristically chosen values if a default value isn't specified explicitly. Joins can be done imperatively as well. This leads to a language that is more clear and optimize-able by hand.

x = binary_search(column_name=id, value=56, show_all_columns=True)

y = dictionary_search(column_name=id, value=56, show_all_columns=True)

z = join(x, y, joinFunc=func(a,b)(a==b))

The example above could be a join. With the search function name itself specifying the index. If no such index is placed over the table it can throw an exception: "No Dictionary Index found on Table"

This is better api design. However, years of optimization and development on SQL implementations makes it so that most no-sql api's will have a hard time catching up to the performance of SQL. It's like googles V8. V8 is a highly optimized implementation of a terrible language (javascript) which is still faster than wasm.

This is still a declarative language, just with the algorithms spelled out explicitly. (or some algorithms, as you didn't specify it for the join).

However, I think specifying the algorithms in the queries is really not a good idea. Your performance characteristics can change over time (or you might now know them at all yet when you start the project). With your solution, if you, e.g. realize later that it makes sense to add a new index, you'd have to rewrite every single query to use that index. With SQL, you simply add the index and are done.

>This is still a declarative language, just with the algorithms spelled out explicitly. (or some algorithms, as you didn't specify it for the join).

Declarative yes, but unlike SQL my example is imperative. An imperative language is easier to optimize then a functional or even a expression based language (SQL) because computers are basically machines that execute imperative assembly instructions. This means the abstractions are less costly and have a better mapping to the underlying machine code.

>However, I think specifying the algorithms in the queries is really not a good idea. Your performance characteristics can change over time (or you might now know them at all yet when you start the project). With your solution, if you, e.g. realize later that it makes sense to add a new index, you'd have to rewrite every single query to use that index. With SQL, you simply add the index and are done.

Because the DB sits over a bottleneck in web development you need to have explicit control over this area of technology. If I need to do minute optimizations then am api should provide full explicit control over every detail. You should have the power to specify algorithms and the language itself should never hide from you what it's doing unless you tell choose to abstract that decision away...

What I mean by "choose to abstract that decision away" is that the language should also offer along with "binary_search" a generic "search" function as well, which can automatically choose the algorithm and index to use... That's right, by changing the nature of the API you can still preserve the high level abstractions while giving the user explicit access to lower level optimizations.

Or you can memorize a bunch of SQL hacks and gotchas and use EXPLAIN to decompile your query. I know of no other language that forces you to decompile an expression on a regular basis just to optimize it. Also unlike any other language I have seen literally postgres provides a language keyword EXPLAIN that allows users to execute this decompilation process as if they already knew SQL has this flaw. If that doesn't stand out like a red flag to you I don't know what will.

One is going to need a way to debug and study ANY complex query for performance and bugs. EXPLAIN is a tool, not a crutch. "Dot-Chained" API's don't solve this by themselves. In bigger shops one typically has skilled DBA's who are good and quick at optimizing SQL anyhow because of their experience with it. An app-side programmer won't (or shouldn't) do this often enough to become a guru with query API optimization. Modern economies rely on specialization.

Read what I am writing. The fact that EXPLAIN is a tool means SQL is a crutch. No other language other than SQL needs this.

Ever profile an application? That's what EXPLAIN helps you with. SQL is different from a lot of languages in that it runs through query planner/executor, often based on properties of the actual data which change over time. Not a lot of other programs do this, and certainly not your typical imperative or procedural code. The JVM is one that comes to mind. Do you know of others?

I KNOW what it helps you with. No programming language has a keyword that decompiles expressions to their base components. Also profiling is only done when optimization is needed.

SQL on the otherhand... EXPLAIN is used on a regular basis, it's built in to the programming language and rather then just mark lines of code with execution time deltas it literally functions as a decompiler to deconstruct the query into another imperative language. This is the problem with SQL.

Many application languages have "reflection" API's that can examine internal or low-level code structure. I used database languages such as dBASE (and clones) that are based on more or less on sequential query languages. While I did like more control over the intermediate steps, including the ability to analyze them; the problem is that different people do things too differently. SQL reigns in the "creativity" to a large extent. What works well for an individual may not scale to "team code". Working with a Picasso-coder's code can be a bear.

This is a valid argument. A high level abstraction definitely serves as a restriction over code that gets to "creative" at the expense of obfuscation. Following this line of logic, the argument then truly does become apples to apples.

> But you probably can't imagine a world without relational databases. I know I can't.

Having worked on a couple projects that used Mongo at the core, I wish I could say the same.

Reply on news.ycombinator.com