Question for the query engine experts

["Wilt, Charles" <WiltC@xxxxxxxxxx>]

I'm wondering if it's considered normal for a complex query with multiple CTE to work fast when broken
up into multiple queries that output to work files.
 
An example:
 
with t1 as (select ... from table1 ...)
      ,t2 as (select ... from table2 ...)
      , t3 as(select ... from t1 .... UNION select ... from t2 ...)
     , t4 as (select .... from table3 ... left outer join table4 ... left outer join table4 )
     , t5 as (select ... from t4 UNION ALL ... select ... from t4 UNION ALL ... select ... from t4)
select ... from ( t5 inner join t3 ...) left outer join table5 .... left outer join table6 ... left
outer join table7
 
works much faster when I break the query into three parts with two physical temporary files:
 
with t1 as (select ... from table1 ...)
      ,t2 as (select ... from table2 ...)
select ... from t1 .... UNION select ... from t2 ...
---> output to tmptable3
 
with t4 as (select .... from table3 ... left outer join table4 ... left outer join table4 )
select ... from t4 UNION ALL ... select ... from t4 UNION ALL ... select ... from t4
---> output to tmptable5
 
 
select ... from ( tmptable5 inner join tmptable3 ...) left outer join table5 .... left outer join
table6 ... left outer join table7
 
 
It would seem to me that the query engine should be faster at creating virtual tables than writing to
physical tables.  
In fact, the CPU processing time is close for the two methods:
1 query - 209 seconds
3 querys w/tmp files - 225 seconds.
 
But clock time was quite a bit different:
1 query - 90min
3 querys w/tmp files - 20min.
 
All the queries were run via Query Manager as batch jobs on a v5r2 box.
 
Thoughts or comments from anyone as to why physical temporary tables were quicker performing than
embedded CTE's?
 
If this always the case? Or does the CTE version possibly need indexes that the physical temporary
table one doesn't?
 
Thanks!
Charles Wilt
 
 
 
 
 
 
 
 
Charles Wilt 

Software Engineer

CINTAS Corporation - IT 92B

513.701.1307

wiltc@xxxxxxxxxx <mailto:wiltc@xxxxxxxxxx> 

 

This e-mail transmission contains information that is intended to be confidential and privileged.  If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful.  Please reply to the message immediately by informing the sender that the message was misdirected.  After replying, please delete and otherwise erase it and any attachments from your computer system.  Your assistance in correcting this error is appreciated.