9 ms·

Your brain must work different to mine. SQL is by far the hardest tool I use. I’ve used all the main languages from asm up to js for real work and nothing break

Your brain must work different to mine. SQL is by far the hardest tool I use. I’ve used all the main languages from asm up to js for real work and nothing breaks my brain like SQL. I use it daily in a business that is heavy on SPs and while I get by and am improving the jump from inner joins and selects to CTEs and the other wizardry is massive. I want to be better at SQL but so many problems I hit up against and think “well that’s a 2 minute job in js/swift/php”

bjo590 6y ago
> I want to be better at SQL but so many problems I hit up against and think “well that’s a 2 minute job in js/swift/php” The thing about SQL is that it's the fastest way to read&write data in a relational database. Maybe writing the code is faster in js/swift/php, but the code will run faster in SQL. If you need to do something to 100M pieces of data you can do a lot worse than SQL.
- danielovichdk 6y ago
  You cannot compare SQL to either of those languages, since they are not built solely for data for data manipulation, but also a lot of other things, which SQL is not. SQL can do a lot too, but just because you e.g can do formatting in SQL doesn't Mean it's the correct place or choice.
- lmm 6y ago
  That's circular reasoning though. Why do you want your data to be in a relational database? Particularly if you're not actually using its features (I don't think I've ever seen a web application that actually got any value out of database-level transactions, for example). A different kind of datastore could offer you better performance and easier querying.
  - Akronymus 6y ago
    I love SQL for making it possible to almost trivially enforce most business rules. Such as: You can only use one of the in another table specified values in this field.
    - lmm 6y ago
      I struggle to understand this viewpoint. In my experience business rules are harder to express in SQL than in practically any first-class programming language. "Is this value one of this list of values" - whether that list is hardcoded or dynamically obtained - is completely trivial. (Of course if you apply some double standard where editing your "source code" requires multiple approvals whereas changing your "database" may be done at will in production then you'll find SQL logic easier to adjust, but that's a reflection of your policies rather than any fundamental reality.)
      - Akronymus 6y ago
        With SQL you can enforce it within the datastore. And if some requirement changes, you can instantly enforce it. Eg.: A new foreign key constraint. Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points.
        lmm 6y ago
        > With SQL you can enforce it within the datastore. And if some requirement changes, you can instantly enforce it. Eg.: A new foreign key constraint. Which is to say that you have little control over deployment. Adding a new foreign key constraint might block queries for an indeterminate amount of time while a new index is built, but you have no way to introspect or predict that at the SQL level (if you're lucky there might be a specific way to find out via the internals of your particular datastore implementation). PHP fans used to tout being able to just edit the code on the production server as an advantage, but most of us recognise it as the opposite. > Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points. If your data is well architected so that you only have a single representation of each piece of data, sure. Equally if your application is well architected you can have a single API where any given thing happens.
        6y ago
  - jimbokun 6y ago
    > (I don't think I've ever seen a web application that actually got any value out of database-level transactions, for example) You only ever have one user in your web applications at a time? > A different kind of datastore could offer you better performance and easier querying. Citation needed. The so called "NoSQL" systems manage to scale better because they have very constrained query models. So they are only "easier", perhaps, in the sense of supporting less functionality, but in most cases that leads to a big increase in complexity in the rest of the application code.
    - lmm 6y ago
      > You only ever have one user in your web applications at a time? No, but database-level transactions don't help you deal with concurrent users. You don't keep a transaction open between showing the user an edit page and applying their changes (at least I hope you don't), your transactions can last at most through the web request-response cycle. So if you want actual transactional behaviour (e.g. a wiki with "another user is editing this page") you have to reimplement it yourself "in userspace". > Citation needed. SQL datastores are very open about the tradeoffs that they make for the sake of ACID isolation - the fact that the transaction isolation level is tuneable shows that, as does the fact that all serious SQL databases use MVCC. Also MySQL had benchmarks showing that 75% of the time for a query by primary key was spent on parsing the SQL. > The so called "NoSQL" systems manage to scale better because they have very constrained query models. So they are only "easier", perhaps, in the sense of supporting less functionality, but in most cases that leads to a big increase in complexity in the rest of the application code. Not my experience, because the overwhelming majority of the time the code doesn't make any use of the complicated SQL functionality. I've literally never seen a cross join in use. I can count the number of times I've seen nontrivial aggregations in live code on one hand. The few times I've written recursive queries I found poor driver support, incompatibilities between different databases. So you pay the cost of flattening your data into the SQL table model, but most of the time you get no benefit from it. There are pretty much three different kinds of queries: indexed lookups into raw data, indexed lookups into derived data (secondary indexes being a special case of this) and aggregations over a full table (table scans being a special case of this). A query model that represents those cases separately is easier to understand and work with, especially when it comes to understanding the performance. If your datastore supports server-side map-reduce style aggregations then you can literally execute arbitrary code in a "query", but you'll have a clear understanding that you're doing something different (with performance implications) from looking up a value by its indexed key.
scotty79 6y ago
The problem for SQL is (apart from horrible syntax that makes any query re-factoring tedious) that databases are very strict structures, that through their rigidity make some operations very fast, but some operations impossible (to perform in this fast manner). When you are crafting good SQL query (crafting is the right word as each non-trivial SQL query is a little puzzle that may take you few days to solve because of the constraints) you need to stay within the bounds of the this fast db world. Whenever you are forced to open a cursor or use CTE for recursion or even have a full table scan you already left the fast land and landed in the world that all general purpose language inhibit, where you have to iterate and recurse and everything takes ages. And in that land any other language beats SQL because any other language has the syntax designed to make things easier in this world while SQL has the syntax that's just good enough for the fast world where operations are highly restricted and when it ventures into the slow world it's just a horrible mess.
jtdev 6y ago
You’re not thinking in sets if you’re constantly inclined to move to js/php/swift. Spend some time trying to break this mental bearier and you’ll find that SQL becomes easier and more sensible.
exabrial 6y ago
I had three college classes that really sealed this up for me: linear algebra, a set theory 'light' class, and a class on database mechanics itself. After than I was able to reason about mathematical sets, and scalar functions and what the database engine is doing. If you "just learn SQL" without understanding the abstraction below it, it'll be difficult to be successful, much the same with anything else.

bjo590 6y ago

> I want to be better at SQL but so many problems I hit up against and think “well that’s a 2 minute job in js/swift/php” The thing about SQL is that it's the fastest way to read&write data in a relational database. Maybe writing the code is faster in js/swift/php, but the code will run faster in SQL. If you need to do something to 100M pieces of data you can do a lot worse than SQL.

danielovichdk 6y ago
You cannot compare SQL to either of those languages, since they are not built solely for data for data manipulation, but also a lot of other things, which SQL is not. SQL can do a lot too, but just because you e.g can do formatting in SQL doesn't Mean it's the correct place or choice.
lmm 6y ago
That's circular reasoning though. Why do you want your data to be in a relational database? Particularly if you're not actually using its features (I don't think I've ever seen a web application that actually got any value out of database-level transactions, for example). A different kind of datastore could offer you better performance and easier querying.
- Akronymus 6y ago
  I love SQL for making it possible to almost trivially enforce most business rules. Such as: You can only use one of the in another table specified values in this field.
  - lmm 6y ago
    I struggle to understand this viewpoint. In my experience business rules are harder to express in SQL than in practically any first-class programming language. "Is this value one of this list of values" - whether that list is hardcoded or dynamically obtained - is completely trivial. (Of course if you apply some double standard where editing your "source code" requires multiple approvals whereas changing your "database" may be done at will in production then you'll find SQL logic easier to adjust, but that's a reflection of your policies rather than any fundamental reality.)
    - Akronymus 6y ago
      With SQL you can enforce it within the datastore. And if some requirement changes, you can instantly enforce it. Eg.: A new foreign key constraint. Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points.
      - lmm 6y ago
        > With SQL you can enforce it within the datastore. And if some requirement changes, you can instantly enforce it. Eg.: A new foreign key constraint. Which is to say that you have little control over deployment. Adding a new foreign key constraint might block queries for an indeterminate amount of time while a new index is built, but you have no way to introspect or predict that at the SQL level (if you're lucky there might be a specific way to find out via the internals of your particular datastore implementation). PHP fans used to tout being able to just edit the code on the production server as an advantage, but most of us recognise it as the opposite. > Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points. If your data is well architected so that you only have a single representation of each piece of data, sure. Equally if your application is well architected you can have a single API where any given thing happens.
        6y ago
- jimbokun 6y ago
  > (I don't think I've ever seen a web application that actually got any value out of database-level transactions, for example) You only ever have one user in your web applications at a time? > A different kind of datastore could offer you better performance and easier querying. Citation needed. The so called "NoSQL" systems manage to scale better because they have very constrained query models. So they are only "easier", perhaps, in the sense of supporting less functionality, but in most cases that leads to a big increase in complexity in the rest of the application code.
  - lmm 6y ago
    > You only ever have one user in your web applications at a time? No, but database-level transactions don't help you deal with concurrent users. You don't keep a transaction open between showing the user an edit page and applying their changes (at least I hope you don't), your transactions can last at most through the web request-response cycle. So if you want actual transactional behaviour (e.g. a wiki with "another user is editing this page") you have to reimplement it yourself "in userspace". > Citation needed. SQL datastores are very open about the tradeoffs that they make for the sake of ACID isolation - the fact that the transaction isolation level is tuneable shows that, as does the fact that all serious SQL databases use MVCC. Also MySQL had benchmarks showing that 75% of the time for a query by primary key was spent on parsing the SQL. > The so called "NoSQL" systems manage to scale better because they have very constrained query models. So they are only "easier", perhaps, in the sense of supporting less functionality, but in most cases that leads to a big increase in complexity in the rest of the application code. Not my experience, because the overwhelming majority of the time the code doesn't make any use of the complicated SQL functionality. I've literally never seen a cross join in use. I can count the number of times I've seen nontrivial aggregations in live code on one hand. The few times I've written recursive queries I found poor driver support, incompatibilities between different databases. So you pay the cost of flattening your data into the SQL table model, but most of the time you get no benefit from it. There are pretty much three different kinds of queries: indexed lookups into raw data, indexed lookups into derived data (secondary indexes being a special case of this) and aggregations over a full table (table scans being a special case of this). A query model that represents those cases separately is easier to understand and work with, especially when it comes to understanding the performance. If your datastore supports server-side map-reduce style aggregations then you can literally execute arbitrary code in a "query", but you'll have a clear understanding that you're doing something different (with performance implications) from looking up a value by its indexed key.

scotty79 6y ago

The problem for SQL is (apart from horrible syntax that makes any query re-factoring tedious) that databases are very strict structures, that through their rigidity make some operations very fast, but some operations impossible (to perform in this fast manner). When you are crafting good SQL query (crafting is the right word as each non-trivial SQL query is a little puzzle that may take you few days to solve because of the constraints) you need to stay within the bounds of the this fast db world. Whenever you are forced to open a cursor or use CTE for recursion or even have a full table scan you already left the fast land and landed in the world that all general purpose language inhibit, where you have to iterate and recurse and everything takes ages. And in that land any other language beats SQL because any other language has the syntax designed to make things easier in this world while SQL has the syntax that's just good enough for the fast world where operations are highly restricted and when it ventures into the slow world it's just a horrible mess.

jtdev 6y ago

You’re not thinking in sets if you’re constantly inclined to move to js/php/swift. Spend some time trying to break this mental bearier and you’ll find that SQL becomes easier and more sensible.

exabrial 6y ago

I had three college classes that really sealed this up for me: linear algebra, a set theory 'light' class, and a class on database mechanics itself. After than I was able to reason about mathematical sets, and scalar functions and what the database engine is doing. If you "just learn SQL" without understanding the abstraction below it, it'll be difficult to be successful, much the same with anything else.