4 ms·

I get the impression that (crucially: outside of Facebook/Google-scale applications) the strongest advocates for No-SQL databases are great software devs who ju

I get the impression that (crucially: outside of Facebook/Google-scale applications) the strongest advocates for No-SQL databases are great software devs who just had a poor experience with SQL databases as juniors or students - or didn’t develop have the necessary experience and skills to make the most of a SQL RDBMS because it was out of their field (e.g. game devs); they’d be missing things like not understanding relational-algebra or relational-calculus - or not even knowing SQL at all - or having the DBA skills to know how to define the best indexes - or even what a cardinality-estimate in an execution plan is. If you throw all of those topics at someone and also add that they only have a week to build the thing - it’s no-wonder that people turn to No-SQL. I’m not putting those people down either - I would have opted for No-SQL myself if I wasn’t already familiar with RDBS theory and management. What I am criticising is the large amount of manual-management needed when you do run an RDBMS because this is putting people off. Instead, things like automatic index generation and pruning, nightly index rebuilds and statistics computing, etc arguably SHOULD be part of the RDBS out-of-the-box and enabled by default. To my knowledge only IaaS databases have features like that (it’s what justifies their subscription pricing). I believe some on-prem database server products will generate recommendations but they won’t automatically implement them on their own accord. Another “approachability” issue I have is with the design of the SQL language: it’s unnecessarily verbose and very confusing for beginners as its basis in relational-calculus instead of relational-algebra throws people off (“SELECT is evaluated after the WHERE clause?!”). If SQL is like SGML and XML, which was replaced in many applications by the faster and lighter JSON format, can we do the same for SQL? Unfortunately there’s too much institutional inertia behind SQL for any of the DBMS vendors to go-it-alone, and beyond trivial queries all of the SQL implementations are incompatible with each other anyway. In summary: 1. RDBMS servers should have an “automatic DBA”-mode enabled by default (because if it’s not the default then the exact people who need it (unsophisticated DB devs) won’t be using it, and the people who don’t want it will just turn it off anyway. 2. SQL needs to be replaced with something better: less verbose but just as expressive, and with all of SQL’s “gotchas” removed (like adding multiplicity assertions to joins, implicit joins when dereferencing a foreign-key, implicit locks, allowing comparisons to NULL, and leaning more towards relational-algebra instead of relational-calculus, and support for column macros: this is desperately needed for CRUD applications!) 3. Thank you for coming to my TED Talk.

ladberg 6y ago
I totally agree with this. I'm relatively new to SQL but have recently had to start messing around with it. In all other CS domains I know exactly what will happen under the hood for any given action, but for SQL I have zero clue. I would love it if I could just write queries and it would automatically add an index or do something else for performance if needed, and possibly report back to me if a query I wrote has particularly bad performance.
- DaiPlusPlus 6y ago
  > I would love it if I could just write queries and it would automatically add an index or do something else for performance if needed, and possibly report back to me if a query I wrote has particularly bad performance. Azure SQL and Amazon RDS do this - but they’re Cloud-hosted IaaS rather than on-prem. On-prem SQL Server does have similar, but limited capabilities with its Query-Store feature - but I don’t recall any of it being fully automatic - at least not by default: which may or may not be a good thing.
- wenc 6y ago
  > In all other CS domains I know exactly what will happen under the hood for any given action, but for SQL I have zero clue. In mathematical modeling, it's the same. You write the model, but what happens under the hood numerically is not that transparent -- in complex cases, you normally don't get to set breakpoints and step through numerical iterations, especially for iterative solvers -- so you have to treat it as a grey box (not black box). You can analyze the problem by analyzing its inputs and outputs, and through the intermediates it exposes. SQL is the same. As are many systems in the physical world. I'd argue the situation in CS is actually more the exception than the norm. > I would love it if I could just write queries and it would automatically add an index or do something else for performance if needed, and possibly report back to me if a query I wrote has particularly bad performance. You can do this by inspecting the query plan. In MS SQL, you can even inspect the plan live (while the query is running) and figure out where the bottlenecks are. For obvious cases, MS SQL's client profiler returns (imperfect) recommendations on which fields to index. The trouble is because the behavior of algorithms depends so much on the characteristics of the data (which in a transactional database is changing all the time) and the underlying intention behind the query (which is not always explicitly declared in SQL -- otherwise the language would very extremely verbose), automated optimization is not always possible. Optimizing query plans is a bit of an art -- it's like trying to give hints to an optimizing compiler via pragmas. Azure SQL does some simple analyses and adds/removes indexes as needed and performance is generally somewhat improved, but if you understand your query patterns, you can actually do far better. For instance, if you know you're going to be joining or filtering on certain fields, indexing those fields will speed up the join. Or when you're doing a nonunique JOIN where just one to join to any one record (doesn't matter which one), you can rewrite that as an OUTER APPLY -- a query optimizer wouldn't be able to deduce your intention of "it doesn't matter which to join to" from your declarative query.

ladberg 6y ago

I totally agree with this. I'm relatively new to SQL but have recently had to start messing around with it. In all other CS domains I know exactly what will happen under the hood for any given action, but for SQL I have zero clue. I would love it if I could just write queries and it would automatically add an index or do something else for performance if needed, and possibly report back to me if a query I wrote has particularly bad performance.

DaiPlusPlus 6y ago
> I would love it if I could just write queries and it would automatically add an index or do something else for performance if needed, and possibly report back to me if a query I wrote has particularly bad performance. Azure SQL and Amazon RDS do this - but they’re Cloud-hosted IaaS rather than on-prem. On-prem SQL Server does have similar, but limited capabilities with its Query-Store feature - but I don’t recall any of it being fully automatic - at least not by default: which may or may not be a good thing.
wenc 6y ago
> In all other CS domains I know exactly what will happen under the hood for any given action, but for SQL I have zero clue. In mathematical modeling, it's the same. You write the model, but what happens under the hood numerically is not that transparent -- in complex cases, you normally don't get to set breakpoints and step through numerical iterations, especially for iterative solvers -- so you have to treat it as a grey box (not black box). You can analyze the problem by analyzing its inputs and outputs, and through the intermediates it exposes. SQL is the same. As are many systems in the physical world. I'd argue the situation in CS is actually more the exception than the norm. > I would love it if I could just write queries and it would automatically add an index or do something else for performance if needed, and possibly report back to me if a query I wrote has particularly bad performance. You can do this by inspecting the query plan. In MS SQL, you can even inspect the plan live (while the query is running) and figure out where the bottlenecks are. For obvious cases, MS SQL's client profiler returns (imperfect) recommendations on which fields to index. The trouble is because the behavior of algorithms depends so much on the characteristics of the data (which in a transactional database is changing all the time) and the underlying intention behind the query (which is not always explicitly declared in SQL -- otherwise the language would very extremely verbose), automated optimization is not always possible. Optimizing query plans is a bit of an art -- it's like trying to give hints to an optimizing compiler via pragmas. Azure SQL does some simple analyses and adds/removes indexes as needed and performance is generally somewhat improved, but if you understand your query patterns, you can actually do far better. For instance, if you know you're going to be joining or filtering on certain fields, indexing those fields will speed up the join. Or when you're doing a nonunique JOIN where just one to join to any one record (doesn't matter which one), you can rewrite that as an OUTER APPLY -- a query optimizer wouldn't be able to deduce your intention of "it doesn't matter which to join to" from your declarative query.