The example is not particularly impressive. The SQL equivalent is much easier to understand, which means it is easier to maintain. Number of lines is not an interesting metric; understandability and maintainability are more important.
A better SQL in 11 lines of code
41–50 of 54 posts
Re: A better SQL in 11 lines of code
#42You’ve got do a better job selling the title sorry. I feel like the separation between a query & the query execution plan is one of the benefits of SQL. I trust the database system to do the right thing 99% of the time, and I don’t want to think about that either really.
The remaining 1% is usually uncomfortable if not down right painful.
But yes, I agree a query optimizer is valuable. Luckily there’s nothing stopping us from implementing one, as Prela is algebraic and all optimization techniques for SQL carry over.
Re: A better SQL in 11 lines of code
#43Earlier quoted context omitted.
They are exactly the same!
Cool! That's both unsurprising, given the apparent similarities, but also a little surprising, since Alloy is built on relational algebra, which you're very careful to distinguish from TAR in your paper. (Great read, btw!)
Thanks! Alloy is also based on TAR, they just call it the more common name of relation algebra (not relational).
Re: A better SQL in 11 lines of code
#44You’ve got do a better job selling the title sorry. I feel like the separation between a query & the query execution plan is one of the benefits of SQL. I trust the database system to do the right thing 99% of the time, and I don’t want to think about that either really.
I feel the opposite way. I very rarely trust the database system to do the right thing. Any query more complex than a basic lookup by primary key requires me to look at query plans and validate that indexes are in place and are being used. Otherwise we risk the production server grinding to a halt. Personally I'd love a more explicit form of SQL that allowed specifying things like "select via scan" or "select via ind…
Most databases that aren't postgres have some sort of query hinting.
Re: A better SQL in 11 lines of code
#45am i the only one who's not afraid of sql taking up lines? sql thats formatted well is beautiful to read my brain enjoys it. it's way easier to read sql in terms of "what resultset is this trying to build" then it is to pick apart some fluent api lookin orm on top of sql
I am in that club. As someone who quite enjoys writing sql but does not like the big sql strings intermingled in the rest of the code I even wrote a clever little python library that loads the queries from files as a function call, that is, you have a file with a pure sql query with parameterized variables and you call it like "for row in sql.video_search(title_like='bridge', date_after='1964-1-1', date_before='1975-…
and i think this is somewhere in the ballpark of a p good approach, treat it as a dataset that has some predefined queries to get what's needed if you're OCD about seeing it in the code
sql is an interface query way to talk to databases, and treating things as datasets where their initialization query lives in a certain spot that flows into something generic/typed or whatever so that it can be wrangled elsewhere in the code with its own bespoke guarantees and handling behaviors is fine i think
i think i just don't think seeing sql in code or near code is a bad thing at all, to me it just means this code talks to a database and its using the database dialect/language to query the data. maybe people see string replacements over the query to apply variables or whatever as a bad thing i dunno, I think it's a relatively simple way to look directly at how a query might be dynamically adjusted on the fly. i dont have to like step through the lineage of methods and whatever other abstractions to formulate _how_ it put a query together. it's definitely annoying that there's some wild west feel to having so many dialects of sql, but it's a hard bet to make that you're going to make a better sql in 11 lines of code. SQL looks different than code because it is different, it's entire function is different, different paradigms are at play it's specifically purposed for querying and it excels at that on every front.
Re: A better SQL in 11 lines of code
#46You’ve got do a better job selling the title sorry. I feel like the separation between a query & the query execution plan is one of the benefits of SQL. I trust the database system to do the right thing 99% of the time, and I don’t want to think about that either really.
The remaining 1% is usually uncomfortable if not down right painful. But yes, I agree a query optimizer is valuable. Luckily there’s nothing stopping us from implementing one, as Prela is algebraic and all optimization techniques for SQL carry over.
Linq, in dot net, is a real life working example of what you’re talking about and it’s agnostic of how the storage is structured.
That said I still rarely use the but the basics of Linq. I just don’t see the upgrade of what you’re solving here.
Re: A better SQL in 11 lines of code
#47You’ve got do a better job selling the title sorry. I feel like the separation between a query & the query execution plan is one of the benefits of SQL. I trust the database system to do the right thing 99% of the time, and I don’t want to think about that either really.
I feel the opposite way. I very rarely trust the database system to do the right thing. Any query more complex than a basic lookup by primary key requires me to look at query plans and validate that indexes are in place and are being used. Otherwise we risk the production server grinding to a halt. Personally I'd love a more explicit form of SQL that allowed specifying things like "select via scan" or "select via ind…
You should investigate how you’re storing & retrieving data. A database system is going to abstract away various things.
If you need direct control of primitives then there’s always fopen as SQLite says.
Re: A better SQL in 11 lines of code
#48The example is not particularly impressive. The SQL equivalent is much easier to understand, which means it is easier to maintain. Number of lines is not an interesting metric; understandability and maintainability are more important.
I’m not sure how they come up with the 20-line example anyway - it’s one join.
Traditionally SQL uses a lot of lines because you put one thing per line, but so what?
Re: A better SQL in 11 lines of code
#49I think an important benefit of a good ORM is to reduce the translations that you have to do between your mental model of the data and what you are trying to do with the data. Before I started working a lot with SQL, ORMs fit my mental model better since I was more used to imperative programming languages and I thought they were easier to work with. Now that I am very comfortable with SQL, I have to translate an ORM…
There is no good ORM -like it says here https://github.com/l3nz/ObjectiveSync “Bad practice - if you hide the database, you may get something done quickly, but it's a bad idea. If yor Java code expects to have a collection of one million objects as an array, it does not matter if they are lazily loaded or not - some code somewhere might want to iterate over them, and this will kill the process. You cannot really forget that there is a database somewhere, and you should not do it.”
Re: A better SQL in 11 lines of code
#50In the article, the linked equivalent query is https://github.com/gregrahn/join-order-benchmark/blob/master... which is written using legacy comma-separated joins and a huge WHERE clause:
SELECT MIN(an.name) AS cool_actor_pseudonym,
MIN(t.title) AS series_named_after_char
FROM aka_name AS an,
cast_info AS ci,
company_name AS cn,
keyword AS k,
movie_companies AS mc,
movie_keyword AS mk,
name AS n,
title AS t
WHERE cn.country_code ='[us]'
AND k.keyword ='character-name-in-title'
AND an.person_id = n.id
AND n.id = ci.person_id
AND ci.movie_id = t.id
AND t.id = mk.movie_id
AND mk.keyword_id = k.id
AND t.id = mc.movie_id
AND mc.company_id = cn.id
AND an.person_id = ci.person_id
AND ci.movie_id = mc.movie_id
AND ci.movie_id = mk.movie_id
AND mc.movie_id = mk.movie_id;
SELECT MIN(an.name) AS cool_actor_pseudonym,
MIN(t.title) AS series_named_after_char
FROM cast_info AS ci
JOIN name AS n ON n.id = ci.person_id
JOIN title AS t ON t.id = ci.movie_id
JOIN aka_name AS an ON an.person_id = n.id
JOIN movie_keyword AS mk ON mk.movie_id = t.id
JOIN keyword AS k ON k.id = mk.keyword_id
JOIN movie_companies AS mc ON mc.movie_id = t.id
JOIN company_name AS cn ON cn.id = mc.company_id
WHERE cn.country_code = '[us]'
AND k.keyword = 'character-name-in-title';
SELECT MIN(an.name) AS cool_actor_pseudonym,
MIN(t.title) AS series_named_after_char
FROM cast_info AS ci
JOIN title AS t ON t.id = ci.movie_id
JOIN aka_name AS an ON an.person_id = ci.person_id
WHERE EXISTS
(
SELECT 1
FROM movie_keyword AS mk
JOIN keyword AS k ON k.id = mk.keyword_id
WHERE mk.movie_id = t.id
AND k.keyword = 'character-name-in-title'
)
AND EXISTS
(
SELECT 1
FROM movie_companies AS mc
JOIN company_name AS cn ON cn.id = mc.company_id
WHERE mc.movie_id = t.id
AND cn.country_code = '[us]'
);
SELECT MIN(an.name) AS cool_actor_pseudonym,
MIN(t.title) AS series_named_after_char
FROM cast_info AS ci
JOIN title AS t FOR KEY (id)