3 ms·

SQL is an abstraction and I wish there was something on top of it supporting loops but that's why we have proper programming languages...

te_chris 7y ago
Recursive ctl’s in Postgres?
- barrkel 7y ago
  Recursive CTEs are great for expressing very poorly performing queries. They might give the right answer, but after you've given up waiting. If you've got tree or graph structures, you're better off denormalizing paths or transitive closures, to reduce the amount of indirection required to answer questions.
rpedela 7y ago
One of the best data tools I have ever used: DBT. The big concept is that everything is a SELECT and handles most DDL and DML under the hood. It also provides the ability to add scripting, such as loops, with Jinja. It is primarily meant for OLAP and ELT, but could be used for some OLTP too. https://github.com/fishtown-analytics/dbt https://github.com/fishtown-analytics/dbt
tfehring 7y ago
You can write loops in (the procedural extensions of) SQL, but IME it's very rare that you'd want to. The bigger issue is the lack of performant abstractions - I copy-paste orders of magnitude more code in SQL than in any other language I've used. There are basically just views and functions, and both can carry substantial (read: multiple orders of magnitude) performance penalties compared to the copy-paste approach, especially when you try to nest or compose them. Materialized views rectify this somewhat but they come with various RDBMS-specific limitations and gotchas.
eyelidlessness 7y ago
If you think of SQL as specifying (without implementing) a pure function operating on a set—rather than imperative steps or statements operating on lists—you'll have a lot better luck reasoning about how to use it to operate on data. CTEs, window functions and joins can all be used to accomplish things you'd do in a loop in an imperative language. It really is a different paradigm, in the same way as logic, functional and object-oriented programming are different paradigms. But if you embrace the paradigm you will surely learn how to accomplish whatever you would in a paradigm more familiar to you.
perl4ever 7y ago
I have rewritten a T-SQL loop that called a query each time through with one query that was a thousand times faster. Seriously.
barrkel 7y ago
The trouble is that any loop you might write may be looping over the wrong thing. Probably the biggest single determinant of a SQL query's performance is the order of joins. The best join order is dependent on how many rows result after each join, which in turn depends on how selective the predicates are that can be applied to each table, and that depends on the data distribution. The database does this with statistics. That means it can change the join order - the nesting level of your respective loops - as the data distribution changes.

te_chris 7y ago

Recursive ctl’s in Postgres?

barrkel 7y ago
Recursive CTEs are great for expressing very poorly performing queries. They might give the right answer, but after you've given up waiting. If you've got tree or graph structures, you're better off denormalizing paths or transitive closures, to reduce the amount of indirection required to answer questions.

rpedela 7y ago

One of the best data tools I have ever used: DBT. The big concept is that everything is a SELECT and handles most DDL and DML under the hood. It also provides the ability to add scripting, such as loops, with Jinja. It is primarily meant for OLAP and ELT, but could be used for some OLTP too. https://github.com/fishtown-analytics/dbt https://github.com/fishtown-analytics/dbt

tfehring 7y ago

You can write loops in (the procedural extensions of) SQL, but IME it's very rare that you'd want to. The bigger issue is the lack of performant abstractions - I copy-paste orders of magnitude more code in SQL than in any other language I've used. There are basically just views and functions, and both can carry substantial (read: multiple orders of magnitude) performance penalties compared to the copy-paste approach, especially when you try to nest or compose them. Materialized views rectify this somewhat but they come with various RDBMS-specific limitations and gotchas.

eyelidlessness 7y ago

If you think of SQL as specifying (without implementing) a pure function operating on a set—rather than imperative steps or statements operating on lists—you'll have a lot better luck reasoning about how to use it to operate on data. CTEs, window functions and joins can all be used to accomplish things you'd do in a loop in an imperative language. It really is a different paradigm, in the same way as logic, functional and object-oriented programming are different paradigms. But if you embrace the paradigm you will surely learn how to accomplish whatever you would in a paradigm more familiar to you.

perl4ever 7y ago

I have rewritten a T-SQL loop that called a query each time through with one query that was a thousand times faster. Seriously.

barrkel 7y ago

The trouble is that any loop you might write may be looping over the wrong thing. Probably the biggest single determinant of a SQL query's performance is the order of joins. The best join order is dependent on how many rows result after each join, which in turn depends on how selective the predicates are that can be applied to each table, and that depends on the data distribution. The database does this with statistics. That means it can change the join order - the nesting level of your respective loops - as the data distribution changes.