7 ms·

> 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

by 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.
        Akronymus 6y ago
        We use exclusively stored procedures, so if we want to add a FK constraint while the application doesn't adhere to it, we do one of two things: Either add it to the application and push out a new version. Or handle it within the stored procedure. Additionally, there would be the option of updating the database when pushing a new software update out. > 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. I think I formulated it badly before. In this case I meant that we have one stored procedure for each thing we do. So that, if the underlying data changes for example, we only need to change the procedures directly acessing it. We can then call those procedures from multiple different parts of the code. And the procedures are all managed within one application, mssms in our case, where you can have a much cleaner structure than within code. In my experience, in PHP for example, you tend to write VERY similar/same queries in multiple locations, which can cause you to find it harder to all instances.
  - scotty79 6y ago
    It makes some things simple and insanely fast at runtime, at the cost of making other things impossible or if possible horribly awkward and just as slow as in any other programming language.
    - Akronymus 6y ago
      In those rare cases, you can do post processing on the data in code if need be. I personally don't see it fully as either/or but as mostly SQL with enhancing the data in rare cases via "traditional" code.
- 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.

by bjo590 6y ago

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.
      - Akronymus 6y ago
        We use exclusively stored procedures, so if we want to add a FK constraint while the application doesn't adhere to it, we do one of two things: Either add it to the application and push out a new version. Or handle it within the stored procedure. Additionally, there would be the option of updating the database when pushing a new software update out. > 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. I think I formulated it badly before. In this case I meant that we have one stored procedure for each thing we do. So that, if the underlying data changes for example, we only need to change the procedures directly acessing it. We can then call those procedures from multiple different parts of the code. And the procedures are all managed within one application, mssms in our case, where you can have a much cleaner structure than within code. In my experience, in PHP for example, you tend to write VERY similar/same queries in multiple locations, which can cause you to find it harder to all instances.
- scotty79 6y ago
  It makes some things simple and insanely fast at runtime, at the cost of making other things impossible or if possible horribly awkward and just as slow as in any other programming language.
  - Akronymus 6y ago
    In those rare cases, you can do post processing on the data in code if need be. I personally don't see it fully as either/or but as mostly SQL with enhancing the data in rare cases via "traditional" code.
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.