Code, frameworks, ORMs, "best practices", patterns, et. al. are ultimately a distraction. There are a million ways to get the data in & out of the database. Moving the bits around is not valuable. There are many overblown software solutions out there that could have been a simple merge statement or CSV import job.
I think that a lot of the misconceptions and bad moods about SQL come out of being forced to work with nasty schemas. The language itself is really domain specific. Consider that one wouldn't complain as much about a super fucked up nested query (and resulting SQL syntax woes) if it wasn't necessary to write such a query in the first place. Aligning tuples and relations to the way the business typically talks about them means you will be less likely to be fighting these things over time. Often, it isn't possible to refactor the schema from zero, but you can put replicas/views around a "bad" schema and target it with your new development & refactors.
And in "modern" SQL this is solved with CTEs. Use them to unwrap the schema the way you want it first, before implementing the business logic.
This dis not arise as a need to solve business mess, but as a need to skip DDL for views/mviews.
You can have arbitrary dimensions sliced in CTEs which does not immediately imply a goal of business schema alignment.
Besides the top SQL devs I’ve met don’t lose time to align schema at all, but write the DB table names and columns as they are, because well… because they were usually the people who created the mess this way.
Many reports such as month-to-month increase of sales or other statistical stuff is much more readable when implemented with CTes. Besides - older versions of DB software didn’t always have window functions or proper ranking, so problems such as top-n were not (and in some occasions still are not) trivial to write unless view/cte is used. We talking pages of single query here, not textbook examples.
So is really about dice and slice in a convenient way, but less about business schema or the ideal E/R
In my experience, normalizing tables is mostly for simplifying inserts. When you write business logic it is rather the opposite, you want non-normalized data because this is where the data is all in one place. And this de-normalization is a great use of CTEs. As well as a general cleanup of the input data, which was my original point.
I wish more people would see the beauty. After a session of SQL, when I take a step back and think.
"Hold on. What I have been doing lately is just pure logic. No library dependency resolution, no concurrency problems (even though massive concurrency is certainly under the hood). No mutability issues. Just logic."
SQL obviously has its warts, some of them serious, like testability. But at the end of the day, I wish all programming was like that. Let the computer decide how to do stuff under the hood. And let the human focus on the logic.
I have somewhat half-assed tried to read up on Prolog for trying to take it to the next level, but failed sofar unfortunately. (It was also a goal to try to unlearn some SQL to avoid getting stuck in some local optimum). Maybe somewhere between SQL and Prolog is the future of programming.
Must be Datalog then ;)
I'm sure I'm wrong and missing something but that's where I stop.
I'm not sure what database technology/data storage that doesn't involve doing those two things to get started.
I haven't done any Prolog, but Datalog which is similar and focused on querying data, and the benefits of Datalog for me is that you can write complex/medium-complex queries a lot easier than in SQL, at least for me. Simpler queries are just less characters, but pretty much the same beyond that.
Probably helps that the Clojure ecosystem embraced Datalog so it mostly feels like writing Clojure code although it's really Datalog. You don't get that same feeling regardless of what SQL library/DSL you use.
it was, it most probably is
I dunno... I've written a tremendous amount of SQL, and I still have to think imperatively (iteratively) in order to write queries that are actually performant, and to know which indexes need to exist.
It would be wonderful if I could just think in terms of set operations, but that tends to result in queries that take 5 minutes to execute rather than 5 milliseconds.
My entire thought process is basically -- what table do I start with, what rows in what order, joining to what, under what conditions, aggregating how, rinse and repeat... It's entirely a mental model of loops and aggregation, never of set operations.
But BigQuery allows you to scale it to 100s of CPUs without having to worry about indexes.
I would do almost any amount of iteration and index tuning to keep the query on a single machine rather than deal with a networked distributed system.
When you get slow queries the real problem is algorithmic complexity and linear workers only can do so much,
But even with BigQuery, you've still got to worry about partioning and clustering, and yes they've even added indexes now.
The only time you really just get to think in sets, is when performance doesn't matter at all and you don't mind if your query takes hours. Which maybe is your case.
But also -- the issue isn't generally CPU, but rather communications/bandwidth. If you're joining 10 million rows to 10 million rows, the two biggest things that matter are whether those 10 million rows are on the same machine, and whether you're joining on an index. The problem isn't CPU-bound, and more CPU's isn't going to help much.
But to me those optimizations are not imperative in nature.
(And BQ will probable eat the 10 million to 10 million join for breakfast...)
People skip ahead to all kinds of nonsense; but most of software engineering is putting the right data into the right format, and moving it around reliably.
I just did a major refactor of a complex distributed code base. I pretty much only count the work I did on schema re-design as the actual “job”, the rest was many hours of coding, but that’s really just implementation.
There are other ways to define schema than SQL of course, but it’s really the perfect way to learn true systems engineering.