Pipe Syntax in SQL

Re:Pipe Syntax in SQL by RaftPeople on Sunday August 25, 2024 @11:02AM (#41347791)

CTE's (at least in MS SQL land) are a syntax level operation, meaning CTE's get expanded to be as if you wrote the same subquery at each place a CTE was, which frequently impacts the optimizer and performance.

I like the idea of CTE's, but I typically use temp tables instead of CTE's to avoid optimizer issues.

[ Parent | on HN ]

Re:Pipe Syntax in SQL by wvenable on Sunday August 25, 2024 @01:12PM (#41349103)

If you use temp tables you're subverting the optimizer. Sometimes that's what you want but often it's not.

[ Parent | on HN ]

Re:Pipe Syntax in SQL by RaftPeople on Sunday August 25, 2024 @04:51PM (#41351306)

I use them on purpose to "help" the optimizer by reducing the search space for query plan ((knowing that query plan optimization is a combinatorial problem and the optimizer frequently can't evaluate enough plans in a reasonable amount of time).

[ Parent | on HN ]