Skip to Content
0
Jul 11, 2016 at 09:26 PM

WITH CLAUSE for sub-query

1134 Views

Hi folks,

Recently I've been performance tuning some calculation views written by another developer and I've consistently found examples where they are using WITH CLAUSE similar to this;

VAR_OUT = WITH <alias_name_A> AS (sql_subquery_statement),
<alias_name_B> AS(sql_subquery_statement_from_alias_name_A
or sql_subquery_statement )
SELECT <column_list>
FROM <alias_name_A>, <alias_name_B>, [tablenames]
[WHERE <join_condition
>]


In several cases I've re-written to be something like this;


LT_ALIAS_A = select * from ABC;

LT_ALIAS_B = select * from XYZ;

VAR_OUT = select * from :LT_ALIAS_A JOIN :LT_ALIAS_B ON whatever


Now both ways work and give results but I'm finding performance seems to be worse with the WITH CLAUSE. Does anybody know for certain that this is consistently the case or perhaps I've just gotten lucky thus far as my examples are somewhat over simplified and in reality these are fairly complex queries however it SEEMS i'm consistently seeing degradation with the WITH clause. I'm not finding much on this clause in SQL guide so I'm wondering if anybody has any thoughts on this or actually does their local tables this way.


Thanks,

-Patrick