4 ms·

I haven’t read this yet but I love your take. Far too often do I see developers doing analytics by slurping an entire table across the network and performing c

I haven’t read this yet but I love your take. Far too often do I see developers doing analytics by slurping an entire table across the network and performing calculations on it in the application. Of course it appears to work in development with tens of kilobytes of records and a local database, but as soon as it’s deployed to production it unleashes chaos. I consider myself very good at SQL but I do prefer to use ORMs for most things. However there’s no arguing that many of them have done a lot to obscure the incredible power inside a relational database, and perhaps go too far in hiding exactly when data is computed remotely vs. being pulled and operated on locally.

blowski 5y ago
I’m very guilty of all those sins. Select all, then use array map, reduce and filter in the app code. I could draw a line in my life where I discovered window functions.
- Cthulhu_ 5y ago
  If the dataset is big enough, those operations actually do make sense because they can be translated to e.g. a Hadoop job - map / reduce / filter are all operations that don't depend on the sorting of a dataset, so in theory the dataset can be sharded across many machines. Of course, that is a solution to a problem that people wish they had. Relational databases can run multi-million row queries in seconds, and then there's BigTable / BigQuery which scales SQL to improbable scales.
  - blowski 5y ago
    Exactly. I'm talking about the "select 5000 rows, serialize them to JSON, and send them over HTTP to the browser, only to render them in a table that never changes" type code.
iamcreasy 5y ago
I have not used ORM, but used some basic SQL. Is ORM more performant? Why should I learn SQL if I can do all the things using ORM?
- moehm 5y ago
  Quite the contrary, ORMs in general are less performant. Using ORMs boils down to two things, convenience and the ability to switch the underlying RDBMS. (For example all the OSS which says you can choose between MySQL, Postgres or SQLite.) But if you support all RDBMS you only can support the smallest intersection between them and can't use advanced features like CTE, window functions, JSON support etc.
  - dragonwriter 5y ago
    > But if you support all RDBMS you only can support the smallest intersection between them and can't use advanced features like CTE, window functions, JSON support etc. That's not true at all, any more that it’s true of supporting all browsers with JS. You can use the advanced features where available, and implement logically (if not performance)-equivalent functionality using more basic functions where the advanced features aren’t available. Or, if you are lazy, just have a reduced feature set available with less capable RDBMS engines. But, on any case, its simply not the case that an ORM that supports engines of varying capacity is limited to using only the least-common-denominator feature set.
    - moehm 5y ago
      Yeah, sure, you can always write raw SQL queries, but that has nothing to do with your ORM, does it? I was talking about ORMs in general, not about the programmer in particular. So yes, you are right that if you have enough engineering resources you can support everything. But most ORMs don't help you with this, so it is not a feature of ORMs. You can always bypass it though. The comparison with JS doesn't fit realy well, because JS is the tool you have to use. You don't have to use ORMs, plain SQL works fine as well.
      - rahimnathwani 5y ago
        When GP says 'you can use the advanced features', 'you' refers to the person developing the ORM, not to a developer using the ORM. So the ORM author can do the hard work of allowing advanced features to work across databases. And it's transparent to the developer using the ORM.
Zababa 5y ago
One argument I've often heard is that since it's easier to scale your application compared to your database, pushing the calculations on your application is a way to get a better scalability in the long term. I wonder how true that is though.
- stouset 5y ago
  I’ve seen applications go down hundreds of times from pulling entire tables locally to compute on them. I’ve seen maybe once where an application grew so successful that scaling the database became an extremely challenging topic. And even in that case, I/O and contention were inevitably the problems. Not CPU. I’ve similarly heard myths that you should be judicious when writing indexes because they can affect insertion performance. I’ve again seen hundreds of cases where under-indexing killed performance and zero where over-indexing caused problems. 99.9% of the time, you’re not the crazy special case. And if somehow you are, the solutions required are going to be nuanced and involve a ton of specific measurement. It’s widely unlikely you’ll accidentally avoid these problems through something like this.
- mongol 5y ago
  Depends a lot. You need to consider the requirements more in detail, and especially if we are considering reads vs writes, and also what are the methods to write the data that needs scalable reads.
- fuy 5y ago
  That depends a lot on the workload, of course. But quite often the DB is not constrained on CPU, but rathen on IO or memory (or even networking), and calculations mostly consume CPU. Moreover, calculations usually require just a small subset of columns in a table, and thus can use indexes efficiently, whereas grabbing all the columns to then filter them in application (because that's how many ORMs work, at least by default) becomes not only worse in terms of memory and networking, but also in terms of IO and CPU required on the DB side. So overall I'm sceptical of that argument, unless there's clear proof from profiling that it's indeed the case.

blowski 5y ago

I’m very guilty of all those sins. Select all, then use array map, reduce and filter in the app code. I could draw a line in my life where I discovered window functions.

Cthulhu_ 5y ago
If the dataset is big enough, those operations actually do make sense because they can be translated to e.g. a Hadoop job - map / reduce / filter are all operations that don't depend on the sorting of a dataset, so in theory the dataset can be sharded across many machines. Of course, that is a solution to a problem that people wish they had. Relational databases can run multi-million row queries in seconds, and then there's BigTable / BigQuery which scales SQL to improbable scales.
- blowski 5y ago
  Exactly. I'm talking about the "select 5000 rows, serialize them to JSON, and send them over HTTP to the browser, only to render them in a table that never changes" type code.

iamcreasy 5y ago

I have not used ORM, but used some basic SQL. Is ORM more performant? Why should I learn SQL if I can do all the things using ORM?

moehm 5y ago
Quite the contrary, ORMs in general are less performant. Using ORMs boils down to two things, convenience and the ability to switch the underlying RDBMS. (For example all the OSS which says you can choose between MySQL, Postgres or SQLite.) But if you support all RDBMS you only can support the smallest intersection between them and can't use advanced features like CTE, window functions, JSON support etc.
- dragonwriter 5y ago
  > But if you support all RDBMS you only can support the smallest intersection between them and can't use advanced features like CTE, window functions, JSON support etc. That's not true at all, any more that it’s true of supporting all browsers with JS. You can use the advanced features where available, and implement logically (if not performance)-equivalent functionality using more basic functions where the advanced features aren’t available. Or, if you are lazy, just have a reduced feature set available with less capable RDBMS engines. But, on any case, its simply not the case that an ORM that supports engines of varying capacity is limited to using only the least-common-denominator feature set.
  - moehm 5y ago
    Yeah, sure, you can always write raw SQL queries, but that has nothing to do with your ORM, does it? I was talking about ORMs in general, not about the programmer in particular. So yes, you are right that if you have enough engineering resources you can support everything. But most ORMs don't help you with this, so it is not a feature of ORMs. You can always bypass it though. The comparison with JS doesn't fit realy well, because JS is the tool you have to use. You don't have to use ORMs, plain SQL works fine as well.
    - rahimnathwani 5y ago
      When GP says 'you can use the advanced features', 'you' refers to the person developing the ORM, not to a developer using the ORM. So the ORM author can do the hard work of allowing advanced features to work across databases. And it's transparent to the developer using the ORM.

Zababa 5y ago

One argument I've often heard is that since it's easier to scale your application compared to your database, pushing the calculations on your application is a way to get a better scalability in the long term. I wonder how true that is though.

stouset 5y ago
I’ve seen applications go down hundreds of times from pulling entire tables locally to compute on them. I’ve seen maybe once where an application grew so successful that scaling the database became an extremely challenging topic. And even in that case, I/O and contention were inevitably the problems. Not CPU. I’ve similarly heard myths that you should be judicious when writing indexes because they can affect insertion performance. I’ve again seen hundreds of cases where under-indexing killed performance and zero where over-indexing caused problems. 99.9% of the time, you’re not the crazy special case. And if somehow you are, the solutions required are going to be nuanced and involve a ton of specific measurement. It’s widely unlikely you’ll accidentally avoid these problems through something like this.
mongol 5y ago
Depends a lot. You need to consider the requirements more in detail, and especially if we are considering reads vs writes, and also what are the methods to write the data that needs scalable reads.
fuy 5y ago
That depends a lot on the workload, of course. But quite often the DB is not constrained on CPU, but rathen on IO or memory (or even networking), and calculations mostly consume CPU. Moreover, calculations usually require just a small subset of columns in a table, and thus can use indexes efficiently, whereas grabbing all the columns to then filter them in application (because that's how many ORMs work, at least by default) becomes not only worse in terms of memory and networking, but also in terms of IO and CPU required on the DB side. So overall I'm sceptical of that argument, unless there's clear proof from profiling that it's indeed the case.