SQL for Distributed Systems

Re:SQL for Distributed Systems by jusssi on Wednesday October 13, 2021 @04:29AM (#28849686)

I find that often the big gains come from applying knowledge about the data. Take a classic, which order should the DB perform the required operations (scan a.foo, scan b.bar, match keys between a and b) for the simple join:

  SELECT * FROM a INNER JOIN b ON b.id = a.bid WHERE a.foo = 'foo' AND b.bar = 'bar'

The correct answer depends on what the tables contain. The query planner makes some educated guesses with what knowledge it has available, but in some cases it guesses consistently wrong.

[ Parent | on HN ]

Re:SQL for Distributed Systems by BenoitP on Wednesday October 13, 2021 @05:23AM (#28850026)

RDBMS have statistics to try addressing that.

IMHO just like machine learning is statistics on steroids, I would not be surprised to see some neural networks and embeddings try to model statistics for RDBMS in the future.

These models could just as well be these that can be used to infer if a user row is bound to add another order row, and which kind of product row to go with that.

[ Parent | on HN ]

Re:SQL for Distributed Systems by tomrod on Wednesday October 13, 2021 @07:38AM (#28850938)

Synthetic data vault is getting there with some of the recent model implementations. A blue ocean project if I ever saw one. Not ready for prod, but is able to learn relationships between tables.

[ Parent | on HN ]

Re:SQL for Distributed Systems by alexisread on Wednesday October 13, 2021 @05:15AM (#28849964)

Yep agreed, and this is the main argument for using functional query languages (like LINQ) as you can control the eval order explicitly.

I'm also a fan of using CTEs in a template, so all template arguments can be grouped together at the top of the query.

Additionally, multiple CTEs in a query execute as a single transaction, and in order (well, for Postgres anyway and probably SQL server) which can allow for multiple (different dependent tables) updates in a query without the roundtrips to say the API layer.

[ Parent | on HN ]

Re:SQL for Distributed Systems by ako on Wednesday October 13, 2021 @08:30AM (#28851295)

How do you rewrite your linq query when the data in your tables change, and the eval order is no longer correct?

[ Parent | on HN ]

Re:SQL for Distributed Systems by alexisread on Wednesday October 13, 2021 @06:03PM (#28858229)

Well you switch to a different linq query as your heuristic changes.

This isn't a linq vs SQL debate. I'm pointing out that linq allows explicit ordering, and if you know the shape of your data, you can account for changes in it.

As with most coding, right tool for the job, YMMV for SQL or linq.

[ Parent | on HN ]