Skip to Content

WITH CLAUSE for sub-query

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jul 12, 2016 at 02:36 AM

    My experience so far doesn't back this up.

    I'd guess that maybe the separate statements get executed in parallel while the WITH clause constructs get executed as a single statement.

    Planviz should be able to confirm/deny that.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Lars,

      I've done some experiments with more simplified versions of these queries and I have seen no obvious performance issues.  The only issue I did find was in one case I could not perform a distinct list of values on the version that used the WITH syntax but not consistently repeatable so I'm still tinkering trying to figure out why that is.

      -Patrick

  • Aug 09, 2016 at 05:31 PM

    Even I also find this is inconsistent and changes from case to case, Can you also check M_ACTIVE_STATEMENTS and see if the sub queries are getting replaced and forms a final query. And if that final query is getting executed? Whether it is doing INLINE or not ?

    Also check if you can apply Hints: NO_INLINE & INLINE in the SQLScript Reference Guide.

    and share your performance results?

    Regards,

    Krishna Tangudu

    Add comment
    10|10000 characters needed characters exceeded