I don't need your query language • Hacker News Board

Re: I don't need your query language

#111

Post by tomnipotent » Sat, Jun 17, 2023, 12:57 PM UTC

mattmanser wrote: post #104

tomnipotent wrote: post #95

Earlier quoted context omitted.

> which costs CPU Which costs very little CPU in 2023. > deserialised on the application server This is true regardless. The low-level libraries are still parsing the stream into meaningful in-memory structures. With JSON, the low-level library only has to parse a variable length string, then JSON decode. I'm unfamiliar with any language in 2023 that doesn't have incredibly fast and efficient JSON parsers. > bandwidt…

No, you can't use 'negigibly' worse as a defence. It's either better, or not. Your comment does not make it better, it's still worse. So it won't improve performance, but degrade it, even if it's negilible. So there's no reason to do it. Plus you've made a crazy SQL select instead of a normal one, which is harder to maintain. So it's worse performance and worse maintenance. i.e. don't do this, it's dumb, especially f…

> you can't use 'negigibly' worse as a defence

Absolutely you can when the increase in something (bandwidth) in a system with surplus supply with the trade-off of optimizing a more constrained supply (CPU or memory).

> made a crazy SQL select instead of a normal one, which is harder to maintain.

Purely subjective. Myself nor the people I've hired would have a problem maintaining a more complex SQL query using CTE's and JSON serialization than not.

> it's worse performance

I cannot imagine that's the case in the context we've been discussing. An RDBMS duplicating JSON output of tuples multiple times in a single transaction is not particularly expensive compared to the alternative.

Re: I don't need your query language

#112

Post by [deleted] » Sat, Jun 17, 2023, 12:57 PM UTC

frogulis wrote: post #11

XorNot wrote: post #5

Earlier quoted context omitted.

FROM users SELECT name, id, location WHERE name LIKE 'a%'; You know I think you're right.

Go further: `FROM users WHERE name LIKE 'a%' SELECT name, id, location` - after all, you can WHERE on things that aren't projected by SELECT

[deleted]

Re: I don't need your query language

#113

Post by jmull » Sat, Jun 17, 2023, 12:58 PM UTC

idoubtit wrote: post #88

ako wrote: post #16

Earlier quoted context omitted.

Always start with the end in mind, first what your goal is then how to achieve it. Also, i don't actually see the problem because you never write a query in a lineair way. Usually start with "select * from table limit 10", look at the columns and data available, and then start refining. By now, code completion works as the table is known. Wouldn't help much to write it table first.

> Usually start with "select * from table limit 10", look at the columns and data available, and then start refining. An experienced person won't do that. For any moderately complex SQL query, before writing it I already have in mind the several jointures I'll need, since I usually know the tables and FK I'm working with. It's like following the edges of a graph, all in my head. But I don't know all the fields of the…

> An experienced person won't do that.

I’m very experienced and do that kind of thing all the time.

Also, your “SELECT 1” technique seems like practically the same thing, except I guess you discover column names from autocomplete tooling rather than query output. (To my mind the difference is inconsequential.)

Re: I don't need your query language

#114

Post by reese_john » Sat, Jun 17, 2023, 1:02 PM UTC

dan-robertson wrote: post #47

I don’t really find the examples convincing. Like, I get that sql could maybe be written in a slightly less horrid way but I would prefer something a lot less horrid. I think I’m much more motivated by analytics queries than the kinds of thing in this example though. I find sql is poorly suited in this case because it is verbose and written backwards, and often requires many layers of subqueries. That said, one can u…

You could probably use window functions

Ex:

  min(sum(size)) over(partition by group) min,
  max(sum(size)) over(partition by group) max

Re: I don't need your query language

#115

Post by conradev » Sat, Jun 17, 2023, 1:03 PM UTC

DaiPlusPlus wrote: post #83

nicoburns wrote: post #43

Earlier quoted context omitted.

This is no longer true in database that have JSON support (which is most of them these days). You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query)

> This is no longer true in database that have JSON support Doing that means losing foreign-key referential integrity...

Generated columns (i.e. some_json->>'id') can have constraints just fine, maintaining whatever integrity you want

Re: I don't need your query language

#116

Post by j16sdiz » Sat, Jun 17, 2023, 1:03 PM UTC

eastbound wrote: post #80

gemstones wrote: post #49

Earlier quoted context omitted.

Yup, this right here. This aspect of SQL is overwhelmingly why I insist on ORMs, too. Any efficiency gains you get by having a senior dev write raw SQL for a complex query are immediately negated by a junior turning what an ORM would write as a single query into three DB calls. All because SQL insists on a flat result set you have to turn into a nested collection yourself, without an ORM doing it for you with eager l…

I’ve had a first-of-class linuxian excellent developer but junior, tell me that we need Kafka because our SQL requests took 3 seconds. It should be a single INSERT, but through an ORM that multiplies it. The only upside of Kafka is not having the ORM…

If you need pre-fetching and 2-phase-commits, a database designed for queues can easily work 3x better than a genetic SQL database.

That's being said, Kafka is not one of them.

Re: I don't need your query language

#117

Post by cfiggers » Sat, Jun 17, 2023, 1:05 PM UTC

mcdonje wrote: post #66

LunicLynx wrote: post #4

The main issue with sql is, that it is the wrong way around, which eliminates all tooling support. You need to state what you want (select a, b, c) before you tell it from where to get it (from). And no tooling can predict that. So switching this, moving from and joins in front of select, might be everything needed to fix sql.

I get around this when hand coding by doing a quick 'SELECT * FROM', adding my joins, then going back and filling in the fields using intellisense. Intellisense doesn't know what columns are available until the FROM clause is handled. To me, this is a slight annoyance with an easy workaround. If the SQL spec gets updated to allow switching the clauses around, I'll be pleased. But I'm not about to change languages ove…

That's exactly what I do too. 'SELECT * FROM', then write the rest of the query, then double back and replace the * at the end.

Is it ideal? No. But it's muscle memory now, so...

¯\_(ツ)_/¯

Re: I don't need your query language

#118

Post by mcdonje » Sat, Jun 17, 2023, 1:06 PM UTC

zX41ZdbW wrote: post #98

mcdonje wrote: post #79

Earlier quoted context omitted.

DuckDB has some really nice syntax updates that I hope get added to the ISO spec, like GROUP BY ALL and GROUP BY aliases. I like their approach of adding thoughtful quality of life improvements instead of coming up with a new language. https://duckdb.org/2022/05/04/friendlier-sql.html

Most of the improvements in the article, including unrestricted aliases, were added in ClickHouse and subsequently influenced DuckDB. They still have to implement many usability and language improvements from ClickHouse.

Didn't know that, thanks.

Re: I don't need your query language

#119

Post by dools » Sat, Jun 17, 2023, 1:07 PM UTC

I feel exactly the same way about ActiveRecord ORMs. That’s why I created PluSQL:

https://github.com/iaindooley/PluSQL

Re: I don't need your query language

#120

Post by pier25 » Sat, Jun 17, 2023, 1:10 PM UTC

Nothing is perfect but given that SQL solves the problem, is ubiquitous, has tons of tooling and educational material, and is extremely mature... It's not going anywhere.

It's not a dinosaur, it's a shark.

Would I like to have something more streamlined and less clunky? Absolutely. But it's going to take a lot of effort for anything to become as ubiquitous as sql.