Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Literate SQL

modern-sql.com

Post reply on HN
Print view View original

41–50 of 100 posts

Re: Literate SQL

#41

Post by collyw »

enjoiful wrote: post #19
caseymarquis wrote: post #8

It's critical to have a good understanding of SQL, but once you do, ORMs with a functional syntax solve this problem for many of us. I know it's not possible for some people to use ORMs as they can't risk the ORM making a performance mistake, but I think the solution is to improve ORMs to the point that writing raw SQL is akin to writing assembly instead of using a higher level language. That said, we're not there ye…

I love Django -- but what it does is mostly magic to me. That being said... I'm fairly competent in being effective in Django -- although not for advanced and efficient querying. I want to get strong in SQL -- where/how do I start?

Practice would be my advice.

First thing to do would be to start printing the queries generated by the ORM to see what it is doing (though it produces some fairly verbose SQL, but its usually easy enough to understand).

Then when you are asked to get some one off numbers out of the database try doing it in SQL. In one query.

Its usually possible but takes a different way of thinking - in sets. Build up queries gradually. Start with the main table. Join in the next table and see the results. Add some conditions in your where clause or join conditions. See what the result is and how it changes.

As for thinking in sets, that is easily said but difficult to translate into words.

I guess describing the set of data that you want back from the database is a good start. My previous team leader would always describe the results in terms of "if x then y" (imperative thinking). When you are find yourself doing that, instead try to describe the data without the "if" statements and instead describe it as "the set where condition x and y are met". That will get you halfway there. Once you start thinking like that you will start to see the beauty in the relational model.

I was a good few years into my career before I started thinking this way - now I try to do as much work in the database as possible - it avoids a whole categories of bugs, usually keeps your code shorter and is one of the easiest ways to improve performance.

Re: Literate SQL

#42

Post by collyw »

3131s wrote: post #38
enjoiful wrote: post #19

Earlier quoted context omitted.

I love Django -- but what it does is mostly magic to me. That being said... I'm fairly competent in being effective in Django -- although not for advanced and efficient querying. I want to get strong in SQL -- where/how do I start?

Write raw SQL queries. My feeling on the Django ORM is that it's useful for defining models and managing migrations, but I prefer to write raw SQL for all queries. The PostgreSQL documentation is really good too.

The ORM takes a whole lot of pain out of updates as well.

Filtering also work fairly nicely for simple stuff, as putting conditions in a dictionary and passing it to filter(kwargs) is a lot nicer than messing about with SQL strings.

I agree that the ORM is limited if you want moderately complex queries though.

Re: Literate SQL

#43

Post by wvenable »

Asooka wrote: post #35
paulddraper wrote: post #21

Earlier quoted context omitted.

I see this opinion a lot. I don't quite understand it. Your DBMS isn't some ambivalent data store with simple universal semantics. I'm not just talking about SQL features like complex subselects or CTEs, but index hints, lock order, transactional visibility, non-trivial column constraints, index locks... These matter in appreciably sized public-facing (e.g. web) systems. Granted, with enough transactions and roundtri…

The reason for me to use ORM rather than direct SQL queries is so I can keep everything written in a single language, so I can develop with one IDE. Having everything under one roof is a huge boon to productivity, rather than having to edit the logic in the IDE, then edit the database queries in a different editor, and the two knowing nothing about each other. It lets me directly map my source language's type system…

Don't most IDE's do SQL in the mix with everything else or am I just spoiled by Visual Studio?

My opinion is that one uses an ORM for different situations than they would use SQL. Many applications can use an ORM and nothing else. Some can use an ORM for basic CRUD tasks and then raw SQL to do analysis or reporting.

Re: Literate SQL

#44

Post by wvenable »

nerdponx wrote: post #5

I wish CTEs ("common table expression", i.e. a "with clause") had the same performance as a subquery in PSQL. I always assumed they'd be implemented as a kind of macro that expanded to a subquery. Is there a good reason for this distinction? For that matter, having some kind of SQL-oriented macro/preprocessor language would be fantastic. I guess GPP (General Preprocessor, https://logological.org/gpp ) is always an op…

Even in databases without the same performance issues as PSQL, like SQL Server, "with clauses" can cause performance issues on their on own.

I recently refactored a long SQL query with a half-dozen with-expressions to a single query and increased the performance by an order of magnitude. They had used "with" to build up a query from a bunch of independent sets and then union them together and, while perfectly logical, it was a performance nightmare. In the end, I just took all the conditions that made up each query and combined it into one with the appropriate joins. I'd even argue that ultimately the finished product was easier to understand.

Re: Literate SQL

#45

Post by lobster_johnson »

nerdponx wrote: post #18
zzzeek wrote: post #13

Earlier quoted context omitted.

You can have functional SQL query languages that map directly to SQL without object oriented row mapping. Everyone can use these tools without performance issues. Disclaimer: i wrote SQLAlchemy.

Is that really a disclaimer, or just the source for your claim? I'm sure you know this, but there are actually several "query builder" tools out there for Python, including SQLAlchemy Core. I'd love to see a side-by-side comparison.

He probably meant "disclosure", not "disclaimer".

Re: Literate SQL

#46

Post by weberc2 »

caseymarquis wrote: post #8

It's critical to have a good understanding of SQL, but once you do, ORMs with a functional syntax solve this problem for many of us. I know it's not possible for some people to use ORMs as they can't risk the ORM making a performance mistake, but I think the solution is to improve ORMs to the point that writing raw SQL is akin to writing assembly instead of using a higher level language. That said, we're not there ye…

I think the solution is to upgrade SQL the language. There's a very low complexity threshold after which you have to trade readability/composability for decent performance (in other words, there are a lot of cases where a human can notice a permissible optimization, but a database can't because it might break correctness for some odd edge case). We're solving that problem with programming languages (notably Rust); I'm not convinced that there's something inherently different about relational databases.

Re: Literate SQL

#47

Post by lobster_johnson »

nerdponx wrote: post #5

I wish CTEs ("common table expression", i.e. a "with clause") had the same performance as a subquery in PSQL. I always assumed they'd be implemented as a kind of macro that expanded to a subquery. Is there a good reason for this distinction? For that matter, having some kind of SQL-oriented macro/preprocessor language would be fantastic. I guess GPP (General Preprocessor, https://logological.org/gpp ) is always an op…

Last I looked, this was coming in either Postgres 10 or possibly the release after. I'm on mobile so I don't have a link, but I believe a new keyword is being added that will change the behaviour of "with".

Edit: Can't find anything. I could have sworn they said there were patches being worked on, however.

Re: Literate SQL

#48

Post by zzzeek »

lobster_johnson wrote: post #45
nerdponx wrote: post #18

Earlier quoted context omitted.

Is that really a disclaimer, or just the source for your claim? I'm sure you know this, but there are actually several "query builder" tools out there for Python, including SQLAlchemy Core. I'd love to see a side-by-side comparison.

He probably meant "disclosure", not "disclaimer".

The overlords of HN have deemed my post is too old for an "edit" button so, yes, my use of "disclaimer" is a case of auditory familiarity overruling accurate terminology

Re: Literate SQL

#49

Post by bayonetz »

Love the literate sql style. A lot of times we have to use temp tables instead of with statements though. In Vertica, all the withs of a query have fit in memory together. Using temp tables instead to create the logical sequence, you only need enough memory for each temp table and final query on there own. The cognitive effect is still the same. Define A, Define B based on A, Define C based on B, etc. but sequentially rather than recursively. When the queries are smaller, withs are more direct, a little less verbose, and usually more performant. However, with the datasets I'm working with, the queries are usually too big or I just don't know how big they will be as I start out writing. So I typically just use temp tables from the outset vs writing with withs only to have to rewrite it once I get "too big to fit in memory" errors.

Re: Literate SQL

#50

Post by weberc2 »

paulddraper wrote: post #21
caseymarquis wrote: post #8

It's critical to have a good understanding of SQL, but once you do, ORMs with a functional syntax solve this problem for many of us. I know it's not possible for some people to use ORMs as they can't risk the ORM making a performance mistake, but I think the solution is to improve ORMs to the point that writing raw SQL is akin to writing assembly instead of using a higher level language. That said, we're not there ye…

I see this opinion a lot. I don't quite understand it. Your DBMS isn't some ambivalent data store with simple universal semantics. I'm not just talking about SQL features like complex subselects or CTEs, but index hints, lock order, transactional visibility, non-trivial column constraints, index locks... These matter in appreciably sized public-facing (e.g. web) systems. Granted, with enough transactions and roundtri…

While I prefer vanilla SQL to the current generation of ORMs, I think ORMs are trying to address a real problem--namely that SQL (despite being high level) isn't very abstraction-friendly. Abstractions in SQL can be expensive and often unpredictably so, despite how clever databases are today. I think part of the problem is the mismatch between the largely imperative OO paradigm and the largely declarative relational paradigm, but I suspect that there's some semantic impedance between SQL and a truly relational model which contributes to the problem.
Post reply on HN