3 ms·

Macros only looks good at first look, but quickly fall flat when need to debug query or any of the underlying components change. The entire approach of CTEs is

by slt2021 4y ago

Macros only looks good at first look, but quickly fall flat when need to debug query or any of the underlying components change. The entire approach of CTEs is flawed, because you are declaring your schema in query - by saying CTE1 is my $orders_query, instead of referring to a view formalized in schema: select * from views_tenders join views_sales join views_items - instead of a sandwich of CTEs using macros with SQL is like using python script to autogenerate classes and interfaces in Java for $my_class and $my_interface, instead of having a formally defined hierarchy of classes and interfaces with methods and signatures

cryptonector 4y ago
You can always macroexpand and debug that. CTEs exist for very good reasons. Chief among those is that they make recursive queries easy to express and understand. But also because it lets you have one command that implements what might look like temp table/view creation, population of those tables, run various queries, and delete said temp schema, but the optimizer gets to see what all you are doing and optimize accordingly (there might be no temp tables in the optimized query). As well CTEs help with readability by letting you abstract sub-queries and outdent them. CTEs do have some downsides, mainly that if you want to reuse them across statements, well, you can't. > using macros with SQL is like using python script to autogenerate classes and interfaces in Java for $my_class and $my_interface, instead of having a formally defined hierarchy of classes and interfaces with methods and signatures We live in a world full of generated code. The alternative is much worse, so we have to be able to debug through generated code.
- slt2021 4y ago
  I agree that CTEs can help compose complex query from several ingredient queries. But once several different queries start sharing common CTE ingredients - this is an indicator that you are relying on implicit(informal) schema, which should be formalized and formalized. Just take your CTE ingredient queries and declare them as views, isolated to your namespace. And let everyone reuse your views, instead of copy-pasting CTE ingredients, or using code-generation to achieve the same. The benefit is single source of truth - there will be only one definition of CTE subquery, and it can evolve/extend independently while letting everyone reuse your parts. I assure you - if you take your CTE with 4 subqueries, and instead create 4 views, the query plan will be the same regardless of using CTEs with code-generation or using views. The benefit is you dont have to use codegeneration, each view will be testable/verifiable

cryptonector 4y ago

You can always macroexpand and debug that. CTEs exist for very good reasons. Chief among those is that they make recursive queries easy to express and understand. But also because it lets you have one command that implements what might look like temp table/view creation, population of those tables, run various queries, and delete said temp schema, but the optimizer gets to see what all you are doing and optimize accordingly (there might be no temp tables in the optimized query). As well CTEs help with readability by letting you abstract sub-queries and outdent them. CTEs do have some downsides, mainly that if you want to reuse them across statements, well, you can't. > using macros with SQL is like using python script to autogenerate classes and interfaces in Java for $my_class and $my_interface, instead of having a formally defined hierarchy of classes and interfaces with methods and signatures We live in a world full of generated code. The alternative is much worse, so we have to be able to debug through generated code.

slt2021 4y ago
I agree that CTEs can help compose complex query from several ingredient queries. But once several different queries start sharing common CTE ingredients - this is an indicator that you are relying on implicit(informal) schema, which should be formalized and formalized. Just take your CTE ingredient queries and declare them as views, isolated to your namespace. And let everyone reuse your views, instead of copy-pasting CTE ingredients, or using code-generation to achieve the same. The benefit is single source of truth - there will be only one definition of CTE subquery, and it can evolve/extend independently while letting everyone reuse your parts. I assure you - if you take your CTE with 4 subqueries, and instead create 4 views, the query plan will be the same regardless of using CTEs with code-generation or using views. The benefit is you dont have to use codegeneration, each view will be testable/verifiable