News for hackers. Stuff that matters.
Re:Literate SQL by bayonetz on Sunday June 25, 2017 @01:38PM (#14631155)
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 by teej on Sunday June 25, 2017 @04:34PM (#14632015)
We do the same thing with Redshift. Using temp tables also gives us more direct control over the query plan, since we can specify distribution and sort keys. On top of that, we can run unit tests on the temporary table (explained in another comment) and fail the query if tests fail.