5 ms·

What in your opinion is a better way of communicating with the database other than SQL?

snuxoll 6y ago
I mean, having a sane protocol designed for use by applications rather than humans would be nice. It’s not that SQL is bad, it’s that mechanically generating it is wrought with peril, gotchas, and a huge mismatch between the code we write to work with the data we retrieve from these sources. Hell, s-expr’s would be a much better format and would require little implementation work.
- ethbr0 6y ago
  How would s-expr's be a better format? (My lisp exposure is minimal)
  - snuxoll 6y ago
    For an application level interface they make sense - they’re trivial to generate and parse in any language, don’t require expensive bespoke parsers and lexers to be written, and the basic constructs are more appropriate for a query interface than something like JSON or other common serialization formats while still being human readable if needed. Again, SQL isn’t a terrible language - it’s just not designed to be mechanically generated in a sane manner. Libraries like jOOQ are useful because they handle a lot of pitfalls with runtime-generated queries, when we could avoid them altogether by having a better way of applications (rather than users) to query and manipulate data.
jeffbee 6y ago
I’ve seen a -lot- of engineering time spent trying to adapt to transient quirks of sql query planners, where the programmer expected a filtered range scan and the database elected for a materialized temp table, or similar disasters. Each and every one of those people would have been better served if they could have just specified the op tree that they wanted to be executed.
- kamranjon 6y ago
  Use EXPLAIN ANALYZE and save yourself some time. It's not that difficult to figure out what the planner is doing and tweak it. I think SQL is the best we got but I'd love to hear what you think is a better alternative.
  - jusssi 6y ago
    I don't think parent was saying they haven't used EXPLAIN ANALYZE or it's equivalent. Explain tools are quite good at showing what the db chose to do. They generally suck at explaining why that particular plan was chosen, or why a particular strategy was not used (i.e. why the table scan when there's a perfectly good index).
    - ethbr0 6y ago
      Isn't that inherent in the abstraction represented by SQL? In that, it's optional that the DBS explains why it chooses what it chose (although it can happily tell you what). Were it to do so, that'd be a pretty big crack in the abstraction layer to peer through, and would likely cause more footgunning by (1) developers peeking at the why, (2) developers assuming stability of the why, & (3) ossification of the underlying DBS engines, as now program functioning depends on their internals behaving a certain way. (Although I guess it's a general win for SQL that we're discussing performance differences, rather than correctness differences)
      - jusssi 6y ago
        As it is, people already have to look into "why" for practical considerations. A database failing to perform will lead to a timeout somewhere else and a 500 error served to an end user. With the lack of tooling support, information is extracted by modifying the query by trial and error (seeing what needs to be changed to flip it over to desired behavior), from dark corners of the internet, and by reading the source code of the storage engine. My latest expedition into these matters was a case where a table scan was technically faster (and favored by the query planner) than a low-cardinality index, but used a lot more CPU. So when the DB was hit with several cases of that particular query simultaneously, the server would run out of CPU and everything slowed to a crawl.
didibus 6y ago
Not saying it's a better choice, but it's an alternative choice I've seen: https://en.m.wikipedia.org/wiki/Datalog#:~:text=Datalog%20is%20a%20declarative%20logic,query%20language%20for%20deductive%20databases https://en.m.wikipedia.org/wiki/Datalog#:~:text=Datalog%20is....

I mean, having a sane protocol designed for use by applications rather than humans would be nice. It’s not that SQL is bad, it’s that mechanically generating it is wrought with peril, gotchas, and a huge mismatch between the code we write to work with the data we retrieve from these sources. Hell, s-expr’s would be a much better format and would require little implementation work.

ethbr0 6y ago
How would s-expr's be a better format? (My lisp exposure is minimal)
- snuxoll 6y ago
  For an application level interface they make sense - they’re trivial to generate and parse in any language, don’t require expensive bespoke parsers and lexers to be written, and the basic constructs are more appropriate for a query interface than something like JSON or other common serialization formats while still being human readable if needed. Again, SQL isn’t a terrible language - it’s just not designed to be mechanically generated in a sane manner. Libraries like jOOQ are useful because they handle a lot of pitfalls with runtime-generated queries, when we could avoid them altogether by having a better way of applications (rather than users) to query and manipulate data.

jeffbee 6y ago

I’ve seen a -lot- of engineering time spent trying to adapt to transient quirks of sql query planners, where the programmer expected a filtered range scan and the database elected for a materialized temp table, or similar disasters. Each and every one of those people would have been better served if they could have just specified the op tree that they wanted to be executed.

kamranjon 6y ago
Use EXPLAIN ANALYZE and save yourself some time. It's not that difficult to figure out what the planner is doing and tweak it. I think SQL is the best we got but I'd love to hear what you think is a better alternative.
- jusssi 6y ago
  I don't think parent was saying they haven't used EXPLAIN ANALYZE or it's equivalent. Explain tools are quite good at showing what the db chose to do. They generally suck at explaining why that particular plan was chosen, or why a particular strategy was not used (i.e. why the table scan when there's a perfectly good index).
  - ethbr0 6y ago
    Isn't that inherent in the abstraction represented by SQL? In that, it's optional that the DBS explains why it chooses what it chose (although it can happily tell you what). Were it to do so, that'd be a pretty big crack in the abstraction layer to peer through, and would likely cause more footgunning by (1) developers peeking at the why, (2) developers assuming stability of the why, & (3) ossification of the underlying DBS engines, as now program functioning depends on their internals behaving a certain way. (Although I guess it's a general win for SQL that we're discussing performance differences, rather than correctness differences)
    - jusssi 6y ago
      As it is, people already have to look into "why" for practical considerations. A database failing to perform will lead to a timeout somewhere else and a 500 error served to an end user. With the lack of tooling support, information is extracted by modifying the query by trial and error (seeing what needs to be changed to flip it over to desired behavior), from dark corners of the internet, and by reading the source code of the storage engine. My latest expedition into these matters was a case where a table scan was technically faster (and favored by the query planner) than a low-cardinality index, but used a lot more CPU. So when the DB was hit with several cases of that particular query simultaneously, the server would run out of CPU and everything slowed to a crawl.

didibus 6y ago

Not saying it's a better choice, but it's an alternative choice I've seen: https://en.m.wikipedia.org/wiki/Datalog#:~:text=Datalog%20is%20a%20declarative%20logic,query%20language%20for%20deductive%20databases https://en.m.wikipedia.org/wiki/Datalog#:~:text=Datalog%20is....