3 ms·

It's because SQL has no performance model. You can't tell by looking at the code (the query) what the performance will be like. For example, you can speed up

by chubot 11y ago

It's because SQL has no performance model. You can't tell by looking at the code (the query) what the performance will be like. For example, you can speed up or slow down code by orders of magnitude by changing a line in a completely different part of the code base (i.e. adding a index). And it's also not an orthogonal change -- increasing your read speed decreases your write speed. The common case for business apps is suboptimal schemas and missing indices. Postgres is fast enough, but it's not trivial to use well. On the other hand, anyone who has programmed in Python or Ruby pretty much has an intuitive feel for how well Redis operations perform. With SQL you get these wild swings, depending on what the query optimizer does. I'm not saying you should use Redis -- it's worth it to learn how to use Postgres. But there's just a lower barrier to entry for non-SQL solutions.

acdha 11y ago
> On the other hand, anyone who has programmed in Python or Ruby pretty much has an intuitive feel for how well Redis operations perform. … have you ever seen that not inevitably lead to wild performance swings as people reinvent SQL in their favorite abstraction layer? The same kind of developers who used to make an ORM demand-load a million objects to calculate a number are now doing that with Redis, Mongo, DynamoDB, etc. and telling themselves that it's okay for it to take 5 minutes to sum a million records because it's Big Data™️. I would posit that you're more likely to see good performance from a database engine with many thousands of engineer-hours poured into optimizations than what the average developer is going to produce reinventing a reporting language in Ruby/Python. Those same developers who can't add an index on a SQL database are not suddenly going to figure out how to maintain denormalized records in Redis.
- crdoconnor 11y ago
  >telling themselves that it's okay for it to take 5 minutes to sum a million records because it's Big Data If it's the kind of query you run every week, it probably is okay. I've not optimized tons of queries like that before - and prevented other developers from doing so - not because it's "big data", but simply because the risk introduced by changing the code and the effort involved in mitigating that risk simply didn't make the optimization worthwhile. In most cases I never would have built it myself from the ground up in such a way that it would take 5 minutes (e.g. without indexes), but somebody else's dumb system that's running in production and solving real human problems in an entirely acceptable time frame is still code that's solving people's problems. And, changes always come with a higher cost and risk than most developers imagine - especially in big systems.
gdulli 11y ago
These things sound like they'd be true of environments with dysfunctional or naive RDBMS practices. While it may not be true for inexperienced developers, an experienced developer can assume proper index use (because it's part of their responsibility to ensure or implement) and has access to sufficient heuristics about how a query will perform. And when it should and shouldn't be assumed to be predictable. (Again, informed by experience.) If you only know the ORM layer, which is unfortunately becoming more common, of course you don't have the full picture and can't make proper decisions.
- andybak 11y ago
  > If you only know the ORM layer If you're using an ORM and you have to care about performance then this might be a necessary skill. However - there are very few times I've really had to do this. Usually either performance isn't a concern or there's a simpler solution than painstaking analysis of SQL query plans - de-normalize, cache or write smarter ORM code.

by chubot 11y ago

acdha 11y ago

> On the other hand, anyone who has programmed in Python or Ruby pretty much has an intuitive feel for how well Redis operations perform. … have you ever seen that not inevitably lead to wild performance swings as people reinvent SQL in their favorite abstraction layer? The same kind of developers who used to make an ORM demand-load a million objects to calculate a number are now doing that with Redis, Mongo, DynamoDB, etc. and telling themselves that it's okay for it to take 5 minutes to sum a million records because it's Big Data™️. I would posit that you're more likely to see good performance from a database engine with many thousands of engineer-hours poured into optimizations than what the average developer is going to produce reinventing a reporting language in Ruby/Python. Those same developers who can't add an index on a SQL database are not suddenly going to figure out how to maintain denormalized records in Redis.

crdoconnor 11y ago
>telling themselves that it's okay for it to take 5 minutes to sum a million records because it's Big Data If it's the kind of query you run every week, it probably is okay. I've not optimized tons of queries like that before - and prevented other developers from doing so - not because it's "big data", but simply because the risk introduced by changing the code and the effort involved in mitigating that risk simply didn't make the optimization worthwhile. In most cases I never would have built it myself from the ground up in such a way that it would take 5 minutes (e.g. without indexes), but somebody else's dumb system that's running in production and solving real human problems in an entirely acceptable time frame is still code that's solving people's problems. And, changes always come with a higher cost and risk than most developers imagine - especially in big systems.

gdulli 11y ago

These things sound like they'd be true of environments with dysfunctional or naive RDBMS practices. While it may not be true for inexperienced developers, an experienced developer can assume proper index use (because it's part of their responsibility to ensure or implement) and has access to sufficient heuristics about how a query will perform. And when it should and shouldn't be assumed to be predictable. (Again, informed by experience.) If you only know the ORM layer, which is unfortunately becoming more common, of course you don't have the full picture and can't make proper decisions.

andybak 11y ago
> If you only know the ORM layer If you're using an ORM and you have to care about performance then this might be a necessary skill. However - there are very few times I've really had to do this. Usually either performance isn't a concern or there's a simpler solution than painstaking analysis of SQL query plans - de-normalize, cache or write smarter ORM code.