3 ms·
The biggest problem with a procedural/explicit query is a dynamic system. Without a query planner, you don't have the luxury of a system rewriting your queries.
by batbomb 11y ago
The biggest problem with a procedural/explicit query is a dynamic system. Without a query planner, you don't have the luxury of a system rewriting your queries. When Table A ~ Table B, but then Table B >> Table A, your queries are going to be radically suboptimal.
Of course, if you're never joining, maybe that's not such a big problem, but you'd have the same issue of a specific range in your table grows disproportionally to another range in the same table and the index you are using is incorrect.
With SQL you can often be very explicit using Common Table Expressions as well in DBs that support it. Otherwise, using subqueries, GROUP BY with HAVING, and several other features often, but not always, prevent radically rewritten queries.
Finally, with SQL on many DBMSs you can still get the explicit wiggle room you need using optimizer hints. No, it's not very portable, but neither is ReQL.
Edit: A few examples of being explicit in SQL (a lá Oracle):
SELECT /*+ INDEX(name) */ *
FROM users
WHERE users.name = 'jorge';
WITH users_by_name AS (
SELECT /*+ INDEX(name) */ *
FROM users
WHERE users.name = 'jorge'
)
SELECT *
FROM users_by_name
JOIN profile using (user_id)
ORDER BY age;
- univerio 11y agoEven if you're not doing any joins, the selectivity of your data matters. It's more efficient to perform a table scan than using an index if the selectivity of your filter is low. I wouldn't want to have to manually rewrite a procedural query when my data changes. I feel like this is one of those "X is categorically better than Y" or "X is better than Y because Z" articles. The problem with these types of articles is that they tend to take an extreme position when the reality is much more nuanced. For the subset of relatively simple problems the author has encountered, perhaps the procedural query is easier to reason about, but there are vastly complex SQL queries that wouldn't benefit from an explicit query like this.
- batbomb 11y agoYeah that's definitely true, I tried to allude to that with the ranges example, "Elvis" would be good for an index, whereas "Chris" or "Matt" you might as well do a full table scan.
- RMarcus 11y ago> The biggest problem with a procedural/explicit query is a dynamic system. Without a query planner, you don't have the luxury of a system rewriting your queries. When Table A ~ Table B, but then Table B >> Table A, your queries are going to be radically suboptimal. This was exactly my thinking. Worse yet, a good query optimizer will rewrite your queries as your data changes. So when tables grow or shrink, indexes are added or removed, and even as the distribution of the data within each table changes, you'll get execution plans that roll with the punches. If you've explicitly codified your execution plan, you don't get any of these advantages. Any changes, and you've got to re-write all your queries. So many of these NoSQL databases leave me shaking my head. Maybe I just don't get it, but I feel like once you understand how RA, DRC, TRC, and SQL are equivalent, you'll never want to write relational algebra again. Cost models aren't perfect but they're pretty good. System R-esque systems can evaluate like, A LOT of join orderings. You don't wanna just throw that power out on the whim that the developer might know better.