3 ms·
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.f
by jusssi 5y ago
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.
- alexisread 5y agoYep 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.
- ako 5y agoHow do you rewrite your linq query when the data in your tables change, and the eval order is no longer correct?
- alexisread 5y agoWell 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.
- BenoitP 5y agoRDBMS 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.
- tomrod 5y agoSynthetic 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.