7 ms·

The test of time isn't always a good metric. html, css and javascript is a huge hack, yet it has withstood the test of time. SQL to me is a huge design flaw de

The test of time isn't always a good metric. html, css and javascript is a huge hack, yet it has withstood the test of time. SQL to me is a huge design flaw despite it's ubiquity. On the web bottlenecks happen at IO and algorithmic searches. Databases are essentially the bottlenecks of the web and how do we handle such bottlenecks? SQL; A high level almost functional language that is further away from the metal than a traditional imperative language. A select search over an index is an abstraction that is too high level to be placed over a bottleneck. What algorithm does a select search execute? Why does using select * slow down a query? Different permutations of identical queries causes slow downs or speed ups for no apparent reason in SQL. SQL is a leaky abstraction that has created a whole generation of SQL admins or people who memorize a bunch of SQL hacks rather than understand algorithms.

deleted 8y ago
[deleted]
bunderbunder 8y ago
One person's trash is another person's treasure. Part of the reason SQL has stood the test of time is the very fact that it allows such a high level of abstraction. The big problem that it solved, compared to much of what existed at the time, was that it allowed you to decouple the physical format of the data from the applications that used it. That made it relatively easy to do two things that were previously very hard: Ask a database to answer questions it wasn't originally designed to answer, and modify a database's physical structure without having to change the code of every application that uses it. A lot of "easier" technologies - including, arguably, ORM on top of relational databases - make things easier by sacrificing or compromising those very features that allow for such flexibility. Which speaks to the grandparent's point about technologies that make it easy to get started in the short term, at the cost of having major disadvantages in the long term.
ljw1001 8y ago
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.
- acjohnson55 8y ago
  > 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.
- crimsonalucard 8y ago
  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)
  - shub 8y ago
    If deciding join order and what indexes to use sounds like your idea of a good time. It certainly isn't mine.
    - crimsonalucard 8y ago
      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.
allwein 8y ago
I strongly disagree with your assessment of SQL. All of the questions that you asked have actual answers, but I read (from what I perceive as your tone) that you think these are all mysteries generated by an unknowable black box. While some of them may be implementation dependent (like "What algorithm does a select search execute?"), others have common and knowable answers. > Why does using select * slow down a query? Because the database first has to perform a translation step to do an initial read from its system tables in order to enumerate the rows to be returned as a result of the final query. > Different permutations of identical queries causes slow downs or speed ups for no apparent reason in SQL. The key word there is "apparent", and again, just because it's not apparent to you, doesn't mean that it's not knowable and apparent to someone else. I also take exception to the concept of "permutations" of "identical" queries. Because if your query is permuted, it's no longer identical. The way you write your SQL has an impact on how it's evaluated. Just because you don't understand the rules, doesn't make it a mystery. As a side note, I'd highly recommend reading up on the Relational Algebra that underpins SQL and other relational databases. https://en.wikipedia.org/wiki/Relational_algebra https://en.wikipedia.org/wiki/Relational_algebra
- crimsonalucard 8y ago
  Those are rhetorical questions. I ask them because these are questions you need to know in order to work well with SQL. I cannot become a master of SQL by only learning SQL. I have to learn the specific implementations, I have to run EXPLAIN... etc... A good abstraction only requires you to know the abstraction not what lies underneath. What we have with SQL is a leaky abstraction. My argument that a high level leaky abstraction placed over a critical bottleneck in the web is a design mistake.
  - mrep 8y ago
    Lol, of course you need a general understanding of how your database works on the inside. There are a million different ways you can store your data and I would argue that choosing your data storage is the most important and tricky decision we have to make as software engineers. Back at my first big tech company, I remember reading the best document I have ever read related to software engineering. It was entirely devoted to choosing your database/storage system. The very first paragraph of the document was entirely devoted to engraining in your head that "choosing a database is all about tradeoffs". They even had a picture where it just repeated that sentence over and over to really engrain it in you. Why? Because every database has different performance characteristics such as consistency, latency, scalability, typing, indexing, data duplication and more. You really need to think about each and every one because choosing the wrong database/not using it correctly usually cause the biggest problems/most work to solve that you will ever have to face.
JustSomeNobody 8y ago
> Different permutations of identical queries causes slow downs or speed ups for no apparent reason in SQL. EXPLAIN does a very good job of explaining why one query is faster than another. > SQL is a leaky abstraction that has created a whole generation of SQL admins or people who memorize a bunch of SQL hacks rather than understand algorithms. This just smacks of sound bite material.
- tabtab 8y ago
  To misquote Winston Churchill, SQL is the worst data approach except for all others. As far as query languages, I'd like to see more language competition. I imagine it would take a decade or two to be competitive with SQL, which has a big head start. (I'm partial to SMEQL myself.)
xg15 8y ago
> A select search over an index is an abstraction that is too high level to be placed over a bottleneck. The high-level was the point because, in the original idea, there was a separation of concerns assumed: The dev writes, in SQL, what the DB should do and the DBA decides how the DB does it. Of course that assumes there is a competent DBA...
- crimsonalucard 8y ago
  High level is a mistake because the slowest part of a web project is the database. It's the bottleneck of the web. The reason why we can use slow languages like python, php or ruby on the web rather than C++ is because the database is 10x slower. Putting a high level leaky abstraction over the bottleneck of the web is a mistake. A language that is a zero cost abstraction is a better design choice.
rapind 8y ago
SQL is declarative and functional. It's only a matter of time before it's the new hotness.
- crimsonalucard 8y ago
  SQL isn't turing complete.

deleted 8y ago

[deleted]

bunderbunder 8y ago

One person's trash is another person's treasure. Part of the reason SQL has stood the test of time is the very fact that it allows such a high level of abstraction. The big problem that it solved, compared to much of what existed at the time, was that it allowed you to decouple the physical format of the data from the applications that used it. That made it relatively easy to do two things that were previously very hard: Ask a database to answer questions it wasn't originally designed to answer, and modify a database's physical structure without having to change the code of every application that uses it. A lot of "easier" technologies - including, arguably, ORM on top of relational databases - make things easier by sacrificing or compromising those very features that allow for such flexibility. Which speaks to the grandparent's point about technologies that make it easy to get started in the short term, at the cost of having major disadvantages in the long term.

ljw1001 8y ago

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.

acjohnson55 8y ago
> 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.
crimsonalucard 8y ago
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)
- shub 8y ago
  If deciding join order and what indexes to use sounds like your idea of a good time. It certainly isn't mine.
  - crimsonalucard 8y ago
    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.

allwein 8y ago

I strongly disagree with your assessment of SQL. All of the questions that you asked have actual answers, but I read (from what I perceive as your tone) that you think these are all mysteries generated by an unknowable black box. While some of them may be implementation dependent (like "What algorithm does a select search execute?"), others have common and knowable answers. > Why does using select * slow down a query? Because the database first has to perform a translation step to do an initial read from its system tables in order to enumerate the rows to be returned as a result of the final query. > Different permutations of identical queries causes slow downs or speed ups for no apparent reason in SQL. The key word there is "apparent", and again, just because it's not apparent to you, doesn't mean that it's not knowable and apparent to someone else. I also take exception to the concept of "permutations" of "identical" queries. Because if your query is permuted, it's no longer identical. The way you write your SQL has an impact on how it's evaluated. Just because you don't understand the rules, doesn't make it a mystery. As a side note, I'd highly recommend reading up on the Relational Algebra that underpins SQL and other relational databases. https://en.wikipedia.org/wiki/Relational_algebra https://en.wikipedia.org/wiki/Relational_algebra

crimsonalucard 8y ago
Those are rhetorical questions. I ask them because these are questions you need to know in order to work well with SQL. I cannot become a master of SQL by only learning SQL. I have to learn the specific implementations, I have to run EXPLAIN... etc... A good abstraction only requires you to know the abstraction not what lies underneath. What we have with SQL is a leaky abstraction. My argument that a high level leaky abstraction placed over a critical bottleneck in the web is a design mistake.
- mrep 8y ago
  Lol, of course you need a general understanding of how your database works on the inside. There are a million different ways you can store your data and I would argue that choosing your data storage is the most important and tricky decision we have to make as software engineers. Back at my first big tech company, I remember reading the best document I have ever read related to software engineering. It was entirely devoted to choosing your database/storage system. The very first paragraph of the document was entirely devoted to engraining in your head that "choosing a database is all about tradeoffs". They even had a picture where it just repeated that sentence over and over to really engrain it in you. Why? Because every database has different performance characteristics such as consistency, latency, scalability, typing, indexing, data duplication and more. You really need to think about each and every one because choosing the wrong database/not using it correctly usually cause the biggest problems/most work to solve that you will ever have to face.

JustSomeNobody 8y ago

> Different permutations of identical queries causes slow downs or speed ups for no apparent reason in SQL. EXPLAIN does a very good job of explaining why one query is faster than another. > SQL is a leaky abstraction that has created a whole generation of SQL admins or people who memorize a bunch of SQL hacks rather than understand algorithms. This just smacks of sound bite material.

tabtab 8y ago
To misquote Winston Churchill, SQL is the worst data approach except for all others. As far as query languages, I'd like to see more language competition. I imagine it would take a decade or two to be competitive with SQL, which has a big head start. (I'm partial to SMEQL myself.)

xg15 8y ago

> A select search over an index is an abstraction that is too high level to be placed over a bottleneck. The high-level was the point because, in the original idea, there was a separation of concerns assumed: The dev writes, in SQL, what the DB should do and the DBA decides how the DB does it. Of course that assumes there is a competent DBA...

crimsonalucard 8y ago
High level is a mistake because the slowest part of a web project is the database. It's the bottleneck of the web. The reason why we can use slow languages like python, php or ruby on the web rather than C++ is because the database is 10x slower. Putting a high level leaky abstraction over the bottleneck of the web is a mistake. A language that is a zero cost abstraction is a better design choice.

rapind 8y ago

SQL is declarative and functional. It's only a matter of time before it's the new hotness.

crimsonalucard 8y ago
SQL isn't turing complete.