4 ms·

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 y

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.
  - crimsonalucard 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. 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.
    - mrep 8y ago
      > 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. 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.
- deleted 8y ago
  [deleted]
- hilbertseries 8y ago
  These problems are not unique to SQL, they are issues with any datastore. And if you're building your architecture correctly, the datastore is always going to be your bottleneck.
  - crimsonalucard 8y ago
    I'm not talking about solving the bottleneck. No api can change that. I'm talking about mitigating the effects of this bottleneck. Namely, SQL is a bad design decision for this area of web dev.

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.
- crimsonalucard 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. 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.
  - mrep 8y ago
    > 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. 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.
deleted 8y ago
[deleted]
hilbertseries 8y ago
These problems are not unique to SQL, they are issues with any datastore. And if you're building your architecture correctly, the datastore is always going to be your bottleneck.
- crimsonalucard 8y ago
  I'm not talking about solving the bottleneck. No api can change that. I'm talking about mitigating the effects of this bottleneck. Namely, SQL is a bad design decision for this area of web dev.