3 ms·

You should learn SQL because it's likely to be a very different language to the ones you already know. In SQL you don't tell the DB how to get the data (for loo

You should learn SQL because it's likely to be a very different language to the ones you already know. In SQL you don't tell the DB how to get the data (for loops and pointers) but what the result should look like, and the DB figures out an execution plan. This style is well worth practising and appreciating.

SilverRed 5y ago
> and the DB figures out an execution plan. And then you spend the next hour trying to decipher the plan and work out how to get it to do something that isn't insanely slow.
- nesarkvechnep 5y ago
  Maybe create foreign keys and indexes in your Rails migrations
  - SilverRed 5y ago
    I had a crazy case where after adding indexes to things, queries suddenly become much much slower. I had to look over the plan and realized that removing these indexes made queries fast again. Turns out you can't just take for granted that an index will speed things up and its something you actually have to test and record before/after results. I'm still slightly at a loss as to what the issue was but the best I can find is that if you index something with lots of duplicate values, you make the query slower as it has to scan the index and then scan the long list of results.
    - scns 5y ago
      Thank you for sharing, something to remember, a valuable lesson.
    - polynox 5y ago
      Indices work best when the number of matching entries is low. There is a metric called index selectivity which is the number of distinct values of the indexed columns divided by the total number of records. For a boolean value, this would be 2/N records, effectively the worst possible index. For a perfect index, it would be 1 (every row has a unique entry in the index). It could be possible for the query planner to get the answer wrong for which index to use if it happens to be wrong about the selectivity of your particular query, because the selectivity must be approximated. See for example PostgreSQL's (a fantastic database) documentation on these approximations and imagine a number of ways it could fail [1]. > Assuming a linear distribution of values inside each bucket, [...] > This amounts to assuming that the fraction of the column that is not any of the MCVs is evenly distributed among all the other distinct values. > Using some rather cheesy assumptions about the frequency of different characters [1] https://www.postgresql.org/docs/current/row-estimation-examples.html https://www.postgresql.org/docs/current/row-estimation-examp...
rdedev 5y ago
When I first encountered SQL i hated it as a language. But then the more I understood the working of RDBMS the more I appreciated its use cases. ETL is probably best handled by the DB if you don't want to get down into optimising the ETL process. I feel having tight loops in SQL and having another programming language as a glue between them is a decent tradeoff but you have to keep data transfer in mind.

SilverRed 5y ago

> and the DB figures out an execution plan. And then you spend the next hour trying to decipher the plan and work out how to get it to do something that isn't insanely slow.

nesarkvechnep 5y ago
Maybe create foreign keys and indexes in your Rails migrations
- SilverRed 5y ago
  I had a crazy case where after adding indexes to things, queries suddenly become much much slower. I had to look over the plan and realized that removing these indexes made queries fast again. Turns out you can't just take for granted that an index will speed things up and its something you actually have to test and record before/after results. I'm still slightly at a loss as to what the issue was but the best I can find is that if you index something with lots of duplicate values, you make the query slower as it has to scan the index and then scan the long list of results.
  - scns 5y ago
    Thank you for sharing, something to remember, a valuable lesson.
  - polynox 5y ago
    Indices work best when the number of matching entries is low. There is a metric called index selectivity which is the number of distinct values of the indexed columns divided by the total number of records. For a boolean value, this would be 2/N records, effectively the worst possible index. For a perfect index, it would be 1 (every row has a unique entry in the index). It could be possible for the query planner to get the answer wrong for which index to use if it happens to be wrong about the selectivity of your particular query, because the selectivity must be approximated. See for example PostgreSQL's (a fantastic database) documentation on these approximations and imagine a number of ways it could fail [1]. > Assuming a linear distribution of values inside each bucket, [...] > This amounts to assuming that the fraction of the column that is not any of the MCVs is evenly distributed among all the other distinct values. > Using some rather cheesy assumptions about the frequency of different characters [1] https://www.postgresql.org/docs/current/row-estimation-examples.html https://www.postgresql.org/docs/current/row-estimation-examp...

rdedev 5y ago

When I first encountered SQL i hated it as a language. But then the more I understood the working of RDBMS the more I appreciated its use cases. ETL is probably best handled by the DB if you don't want to get down into optimising the ETL process. I feel having tight loops in SQL and having another programming language as a glue between them is a decent tradeoff but you have to keep data transfer in mind.