4 ms·
SQL may be a language for all those things, but unfortunately it's horribly bad at it. Wilfully obtuse syntax. Incredibly poor compositionality. No testability
by lmm 3y ago
SQL may be a language for all those things, but unfortunately it's horribly bad at it. Wilfully obtuse syntax. Incredibly poor compositionality. No testability worth the name. The deployment model is a half-baked pile of perl scripts, and that's if you're lucky.
So yeah, I use SQL like https://xkcd.com/783/ https://xkcd.com/783/ . I'm sure you have a bunch of cool data analysis tools in there, but please just shut up, give me the contents of my table, and let me process it in a real programming language where I have map/reduce/filter as regular composable functions that I can test and reuse, with a syntax that doesn't make my eyes bleed.
- ttfkam 3y agomap/filter/reduce? Give you the contents of your table, so you can process it in the app tier with map/filter/reduce?! Ah, the hubris of devs who honestly believe they can whip out a solution in a day that beats a dedicated army of developers singularly focused on the task of large data management, storage, and serialization. And almost always forgetting that serialization off of disk and over a network isn't free. There's a reason why SQL is going on 50 years when most technologies are lucky to remain dominant past 10 in this industry. And if you think it's just because of inertia or lack of imagination, you're deluded. SQL isn't perfect (nothing is), but as a DSL for set theory, it does a damn good job, even 50 years later. Far better than any map/filter/reduce whipped up yet again by someone who doesn't fully understand the scope of the problems being solved. It's doubly troubling when you can't grok that SELECT = map, WHERE = filter, and GROUP BY + aggregator = reduce. I sincerely hope you aren't avoiding JOIN by loading both tables ahead of time.
- roflyear 3y agoYeah, if your devs are loading entire tables into code and using filter on them ... I haven't personally seen that, but oh god. Not that it isn't valid in some cases. It is. If for example, you're using all of the table in your logic and need to filter on to do something like pull out certain items for a certain need. Sure. Then it is good, absolutely!
- lmm 3y ago> Ah, the hubris of devs who honestly believe they can whip out a solution in a day that beats a dedicated army of developers singularly focused on the task of large data management, storage, and serialization. A "dedicated army of developers" who've been "going on 50 years" but whose flagship solutions are still single-point-of-failure, still noncompositional, still untestable, still have bizarre and incomprehensible performance characteristics. Yeah, no, I'm going to do stuff in regular application code, thanks.
- Spivak 3y agoYou pay a huge cost in moving those bits over the network though. How do you even deal with tables that don't fit in memory or the lack of indexes for tables where sequential scanning is too slow?
- lmm 3y agoI mean at that point you're getting into real big data stuff. Move the code to the data rather than moving the data to the code; have some way to stream in the data in its native format rather than having to read it in; do pre-aggregation and indexing as the data is written (but not in a way that's blocking your OLTP). Which, yes, is stuff that SQL RDBMSes do for you up to a point, but they do it in invisible and unmanageable ways; IME you're better off doing it explicitly and visibly. In seriousness SQL databases can be good for ad-hoc exploratory queries, so having your data processing pipeline spit out a read-only SQL database dump on a regular schedule is worthwhile, but using that for anything more than prototyping is a mistake.
- nologic01 3y agoI am not particularly opinionated on the matter and i think i can appreciate both sides of the argument. SQL is somewhat analogous to C. It is entirely legitimate to look at its archaic usability features and wonder if we could not do better and at the same time it has hit a sweet spot of adaptation to the domain that ensured its longevity. Something that might shake up things a bit is graph databases / query languages. We can think of them as a generalization of the sql universe and an opportunity to modernize it
- mcdonje 3y agoSQL is a declarative language. If you ask it for an address, it'll give you an address. Not sure what you're trying to say with that xkcd. SQL is a real language. It is testable and reusable. It's a DSL, so the syntax isn't something you have to look at most of the time unless you're a DBA. That said, not everyone hates the syntax. You're probably losing efficiency in your quest for functional paradigm perfection.
- roflyear 3y agoBeing fair, there really aren't tools built to test SQL queries like there are to test other code.
- freetime2 3y agoWe use integration tests to test our SQL queries. Basically fire up our persistence layer and a database instance together, call some create/update methods, and assert that the expected results are returned. Writing tests is pretty straightforward - the tests proved incredibly valuable recently when we migrated to a different RDBMS provider. But yeah I guess we don't really have a good way to write automated tests for migrations that run outside of our application code.
- ttfkam 3y agoDDL is like code. DML is not. Tools like Sqitch are quite adept at testing DDL. DML is declarative. Like HTML. SQL is a DSL for set theory. Do you test your HTML like the rest of your code too? But to be clear, Sqitch and pg_tap (off the top of my head) are both effective testing tools for Postgres databases. MS SQL and Oracle both have extensive testing frameworks available.
- roflyear 3y agoSure. HTML doesn't impact as much as the app as SQL does. Missing records in a SQL query can have a huge impact on many systems. A missing table row (HTML) doesn't usually impact that much.
- alex_lav 3y ago> SQL may be a language for all those things, but unfortunately it's horribly bad at it. Any piece of software used incorrectly will seem "horribly bad". > Wilfully obtuse syntax This is an opinion. > Incredibly poor compositionality. Another opinion. > No testability worth the name I would argue that "try it, observe side effects and rollback" as a language feature is more testable than many/most programming languages. > The deployment model is a half-baked pile of perl scripts, and that's if you're lucky. >> Any piece of software used incorrectly will seem "horribly bad". I appreciate that you hate it. I don't even think you should care or learn it if you don't want to. But I think positioning things you don't understand or _want to use_ as "horrible" or "bad" or whatever is acting in bad faith. You're allowed to say that you don't like something and don't want to learn how to use it, without suggesting it's the tool's fault. > So yeah, I use SQL like https://xkcd.com/783/ https://xkcd.com/783/ . I'm sure you have a bunch of cool data analysis tools in there, but please just shut up, give me the contents of my table, and let me process it in a real programming language where I have map/reduce/filter as regular composable functions that I can test and reuse, with a syntax that doesn't make my eyes bleed. This comment directly demonstrates "I don't understand, I don't want to, just let me do whatever I want however I want", which is fine, but again, not the tool's fault you don't want to use it. Quoting again because it's also relevant to this last comment: >> Any piece of software used incorrectly will seem "horribly bad".
- lmm 3y agoAt some point there is an objective underlying reality. I've worked at a lot of places and never seen SQL done well; even the people who were happy with SQL had no automated testing, no compositionality to speak of, and a crappy deployment model (and would usually acknowledge all this! They just somehow didn't mind). If one person uses a tool badly that's maybe a problem for that person, but if everyone uses the tool badly that's a problem with the tool.
- alex_lav 3y ago“Everyone” in this context means “the places one individual person is aware of”, and when compared to _actual_ “everyone”, is mostly irrelevant though. The things you’re complaining about are solved problems, you just don’t care/refuse to acknowledge those solutions, it seems. Which is fine! Like I said, you can just say you don’t like something without trying to smear it.
- laeri 3y agoWith small amounts of data that may work doing in memory but you can't scale without doing calculation in SQL that is optimized for it.