111–120 of 243 posts
>The Only Programming Language Built on Mathematics, Not Fashion As a modern array language D4M is the natural successor for SQL [1]. D4M is based on mathematics like SQL, specifically associative array algebra but not relational unlike SQL. It's more generic since can it caters to most modern data abstractions including spreadsheets, database tables, matrices, and graphs [2]. You can achieve 100M database inserts pe…
I’ve always felt that SQL is somewhat easy to grasp for basic queries, but gets complex and difficult for even moderate to higher complexity use cases. My eyes glaze over when I read long stored procedures that someone else has written. Any recommended resources to go from beginner/beginner-intermediate to advanced?
I find those big stored procedures usually fall into two categories; logic that should be in the DB, but should be decomposed (staging tables, other SPs, etc) in which case they can be understandable in chunks; or logic that shouldn't be in the DB but has been shoved in there, in which case there's more of an ideological debate but I generally prefer to pull out and run in the application layer. (the latter is pretty much IMO the things that you've done after you've gotten the data at the right grain, when you are massaging it to a particular form/presentation format; performance is often the final arbitre here though).
I’d say the most impactful thing is not to learn SQL, but set theory. Well-written SQL is about thinking in sets. I cannot tell you how many poorly written procedural stored procedures I’ve replaced with a single performant SQL query over the years. This is because the most impressive part of the SQL ecosystem is the DBMS engine’s query plan. Though, yes, you have to know how to influence it. I find ORMs also tend to…
What had a big impact on me was the relational model, specifically after reading Richard Fabian's Data-oriented Design book [1]. I had watched Mike Acton's famous Data-oriented Design talk [2], then Andrew Kelley's talk [3] where he explains speedups in the Zig compiler using DoD principles (largely using methods from Acton's talk), but Fabian's book tied these concepts to database normalization and the relational model.
Most DoD advice is very "exercise left to the reader", because it's about matching a specific problem, but using the relational model and considering your data's primary and foreign key relations can be really powerful. I just wish more of that power was exposed through regular programming language interfaces, rather than having to pull in and marshal data through a DB. I might have to try C# and Linq.
1. https://www.dataorienteddesign.com/dodbook/
Earlier quoted context omitted.
A couple of sites worth checking out to level up, both by Markus Winand: https://modern-sql.com/ https://use-the-index-luke.com/
also a few different para-sql languages that can be useful, to lower the complexity: - https://prql-lang.org/ - https://www.malloydata.dev/
Not quite as simple as learning it once. SQL evolves like other languages, across vendor implementations. The ClickHouse and DuckDB dialects for example extend the language with analytic options not found in ANSI SQL, nor T-SQL, Pl/PgSQL, etc. DuckDB QoL enhancements are greatly missed when not available.
Just, for god's sake, move SELECT after GROUP BY, I beg you.
Current structure makes sense to me. SELECT .... what do I want FROM .... where is it WHERE .... what filters do I want to apply GROUP BY .... how do I want it aggregated Maybe it's just that I'm so used to it. I could see FROM being first, that would actually make a little more sense to me.
What people often want:
Everyone knows SQL already. The harder parts that pay off are schema design, knowing how to interact with your DB in code, and knowing all the ins and outs of whatever DBMS you're using.
I would emphasize the importance of batching and set operations. This is where I think many developers lose track of the rabbit, because you don't have much control over either of these things via ORMs. You have to get your hands dirty with raw command text. The value of this stuff is difficult to overstate. Batching allows for you to rapidly load the RDBMS. The first few times you test, it will probably go so fast y…
I think the pretext of this articles is ridiculous. Yes, SQL is based around relational algebra, but all programming languages are built on a theoretical foundation. And SQL is very much a "fad" language - it just somehow managed to stick around. The goal was not some sort of mathematical purity, but rather to built a natural language data interface (sounds like something currently very hyped?) and it failed spectacu…
Eh... Where did you find `==` used in SQL?