> 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
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.
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.
You aren't responding to my argument, everything you said is something I already know. So lol to you. You're making a remark and extending the conversation without addressing my main point. I'm saying that the fact that you need "a general understanding of how a database works on the inside" is a design flaw. It's a leaky abstraction.
A C++ for loop has virtually the same performance across all systems/implementations; if I learn C++ I generally don't need to understand implementation details to know about performance metrics. Complexity theory applies here.
For "SELECT * FROM TABLE", I have to understand implementation. This is a highly different design decision from C++. My argument is that this high level language is a bad design choice to be placed over the most critical bottleneck of the web: the database.
The entire reason why we can use slow ass languages like php or python on the web is because the database is 10x slower. Database is the bottleneck. It would be smart to have a language api for the database to be highly optimize-able. The problem with SQL is that it is a high level leaky abstraction so optimizing SQL doesn't involve using complexity theory to write a tighter algorithm. It involves memorizing SQL hacks and gotchas and understanding implementation details. This is why SQL is a bad design choice. Please address this reasoning directly rather than regurgitating common database knowledge.
> For "SELECT * FROM TABLE", I have to understand implementation
This is not even remotely an apples-to-apples comparison. One is a fairly simple code construct that executes locally. The other is a call to a remote service.
It doesn't matter if the language you use to write it is XML, JSON, protocol buffers or SQL, any and all calls across an RPC boundary are going to have unknown performance characteristics if you don't understand how the remote service is implemented. If you are the implementer, and you still choose not to understand how it works, that's your choice, not the tool's. Every serious RDBMS comes with a TFM that you can R at any time. And there are quite a few well-known and product-agnostic resources out there, too, such as Use the Index Luke.
Alternatively, feel free to write your own alternative in C++ so that you can understand how it works in detail without having to read any manuals. It was quite a vogue for software vendors to sink a few person-years into such ventures back in the 90s. Some of them were used to build pretty neat products, too. Granted, they've all long since either migrated to a commodity DBMS or disappeared from the market, so perhaps we are due for a new generation to re-learn that lesson the hard way all over again.
>It doesn't matter if the language you use to write it is XML, JSON, protocol buffers or SQL, any and all calls across an RPC boundary are going to have unknown performance characteristics if you don't understand how the remote service is implemented.
Dude, then put your database on a local machine and execute it locally or do an http RPC call to your server and have the web app run a for loop. Whether it is a remote call or not the code gets executed on a computer regardless. This is not a factor. RPC is a bottleneck but that's a different type of bottleneck that's handled on a different layer. I'm talking about the slowest part of code executing on a computer not Passing an electronic message across the country.
So whether you use XML, JSON, or SQL it matters because that is the topic of my conversation. Not RPC boundaries.
>If you are the implementer, and you still choose not to understand how it works, that's your choice, not the tool's. Every serious RDBMS comes with a TFM that you can R at any time. And there are quite a few well-known and product-agnostic resources out there, too, such as Use the Index Luke.
I choose to understand a SQL implementation it because I have no choice. Like how a front end developer has no choice but to deal with the headache that is CSS or javascript.
Do you try to understand how C++ compiles down into assembler? For virtually every other language out there in existence I almost never ever have to understand the implementation to write an efficient algorithm. SQL DBs are the only technologies that force me to do this on a Regular Basis. Heck they even devoted a keyword called 'EXPLAIN' to let you peer under the hood. Good api's and good abstractions hide implementation details from you. SQL does not fit this definition of a good API.
If that doesn't stand out like a red flag to you, then I don't know what will.
>Alternatively, feel free to write your own alternative in C++ so that you can understand how it works in detail without having to read any manuals. It was quite a vogue for software vendors to sink a few person-years into such ventures back in the 90s. Some of them were used to build pretty neat products, too. Granted, they've all long since either migrated to a commodity DBMS or disappeared from the market, so perhaps we are due for a new generation to re-learn that lesson the hard way all over again.
In the 90s? Have you heard of NOSQL? This was done after the 90s and is still being done right now. There are alternative implementations to database API's that DON'T INVOLVE SQL. The problem isn't about re-learning, the problem is about learning itself. Learn a new paradigm rather than remark to every alternative opinion with a sarcastic suggestion: "Hey you don't like Airplanes well build your own Airplane then... "
And I just said that "choosing a database is all about tradeoffs" which you need to understand (aka: the leaky abstractions).
> A C++ for loop has virtually the same performance across all systems/implementations
> For "SELECT * FROM TABLE", I have to understand implementation.
No you don't, it has the same performance: a for loop. However, by grouping all of your data onto 1 server, for loops are much more costly than the likely orders of magnitude more regular servers you have than a database. Fortunately, your SQL database supports indexes which speed up those queries. Granted, I'm no database expert, but adding the right indexes and making sure your queries utilize them have solved pretty much every scaling problem I have thrown at them.
> It would be smart to have a language api for the database to be highly optimize-able. The problem with SQL is that it is a high level leaky abstraction so optimizing SQL doesn't involve using complexity theory to write a tighter algorithm. It involves memorizing SQL hacks and gotchas and understanding implementation details.
It is optimizable and 90% of those optimizations I have made simply involve adding an index and then running a few explains/tests to make sure you are using them properly.
If you'll only answer me this though, what database would you recommend than? I'm dying to know since you think you know better and google, a company that probably has more scaling problems than anyone else, doubled down on SQL with spanner which from what I have read, requires even more actual fine tuning.
And I'm saying the tradeoff of using a leaky abstraction is entirely the wrong choice. A hammer vs a screwdriver each have tradeoffs but when dealing with a nail, use a hammer, when dealing with a screw use a screw driver. SQL is a hammer to a database screw.
>No you don't, it has the same performance: a for loop. However, by grouping all of your data onto 1 server, for loops are much more costly than the likely orders of magnitude more regular servers you have than a database.
See you don't even know what algorithm most SQL implementations use when doing a SELECT call. It really depends on the index but usually it uses an algorithm similar to binary search off of an index that is basically a binary search tree. It's possible to index by a hash map as well, but you don't know any of this because SQL is such a high level language. All you know is that you add an index and everything magically scales.
>Fortunately, your SQL database supports indexes which speed up those queries. Granted, I'm no database expert, but adding the right indexes and making sure your queries utilize them have solved pretty much every scaling problem I have thrown at them.
Ever deal with big data analytics? A typical SQL DB can't handle the million row multi dimensional group bys. Not even your indexes can save you here.
>It is optimizable and 90% of those optimizations I have made simply involve adding an index and then running a few explains/tests to make sure you are using them properly.
I don't have to run an EXPLAIN on any other language that I have ever used. Literally. There is no other language on the face of this planet where I had to regularly go down into the lower level abstraction to optimize it. When I do it's for a rare off case. For SQL it's a regular thing... and given that SQL exists at the bottleneck of all web development this is not just a minor flaw, but a huge flaw.
>If you'll only answer me this though, what database would you recommend than? I'm dying to know since you think you know better and google, a company that probably has more scaling problems than anyone else, doubled down on SQL with spanner which from what I have read, requires even more actual fine tuning.
I don't know if you're aware of CSS or javascript and the glaring flaws everybody complains about front end web development but it's a good analogy to what you're addressing here. Javascript and CSS are universally known to have some really stupid flaws yet both technologies are ubiquitous. No one can recommend any alternative because none exists. SQL is kind of similar. The database implementations and domain knowledge have been around so long that even alternative NO-SQL technologies have a hard time over taking SQL.
Which brings me full circle back to the front end. WASM is currently an emerging contender with javascript for the front end. Yet despite the fact that WASM has a better design then JS (not made in a week) current benchmarks against googles V8 javascript engine indicate that WASM is slower then JS. This is exactly what's going on with SQL and NO-SQL. Google hiring a crack team of genius engineers to optimize V8 for years turning a potato into a potato with a rocket booster has made the potato faster then a formula one race car (WASM)
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.
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.
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...
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.
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.
which to you would be more clear?
SELECT * FROM TABLE WHERE id = 56
binary_search(column_name=id, value=56, show_all_columns=True)
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.
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.
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.
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.
Having worked on a couple projects that used Mongo at the core, I wish I could say the same.
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.