Re: SQL CTE vs NTE (with LATERAL?)

Hi Charles,

I use a ton of LATERAL joins, and am very happy with their flexibility and
typically their performance.

Try this one, but I can't really predict if it will perform better. My
guess is it will.

select h.inv#, h.tran_date
, d1.linetype, d1.col1A_tot, d1.col1B_tot
, d2.linetype, d2.col2A_tot, d2.col2B_tot

from header h

left join lateral (
select d1.inv#, d1.linetype
, sum(d1.colA) as col1A_tot
, sum(d1.colB) as col1B_tot
from detail1 d1
where d1.inv# = h.inv#
group by d1.inv#, d1.linetype
) as d1 on 1 = 1

left join lateral (
select d2.inv#, d2.linetype
, sum(d2.colA) as col2A_tot
, sum(d2.colB) as col2B_tot
from detail2 d2
where d2.inv# = h.inv#
group by d2.inv#, d2.linetype
) as d2 on 1 = 1

where h.tran_date between '2016-11-01' and '2016-11-30'

Mike


date: Tue, 18 Apr 2017 10:22:51 -0600

from: Charles Wilt <charles.wilt@xxxxxxxxx>
subject: SQL CTE vs NTE (with LATERAL?)

I'm working on creating a view to simplify access to some data for our BI
solution...

I've currently got it working with CTEs. Takes about a minute to
run...which isn't bad since the RPG report that provides the same data
takes 4 minutes.

But I think I can do better. A simplified example of the CTE form is like
so

with
hdr as (
select inv#, tran_date
from header
), dtl1 as (
select inv#, linetype
, sum(colA) as col1A_tot
, sum(colB) as col1B_tot
from detail1
group by inv#, linetype
), dtl2 as (
select inv#, linetype
, sum(colA) as col2A_tot
, sum(colB) as col2B_tot
from detail2
group by inv#, linetype
), sim_view as (
select inv#, tran_date, linetype
, col1A_tot, col2A_tot, col2B_tot
from hdr
left outer join dtl1
on hdr.inv# = dtl1.inv#
left outer join dtl2
on hdr.inv# = dtl2.inv#
)
select *
from sim_view
where tran_date between '2016-11-01' and '2016-11-30'

Note: the last CTE and the select are for testing purposes...as data will
most likely be pulled from the actual view a month at a time.

This example is very basic, the actual SQL has 11 CTEs (not counting
sim_view) and is 332 lines. :/

I've always preferred CTE to NTE as I think the CTE's are easier to
follow. Additionally, I've always felt there was little difference in
performance between CTE and NTE. As I understand it the optimizer uses the
same plan regardless. I suspect that while that may be true for relatively
simple queries, it may not be true for this one.

Looking at VE, I see that a full table scan is being done on detail1 and
detail2. I'm not accessing 30% or more of the records, so that's not the
reason for the table scan. I was hoping the optimizer would build a list
of inv# from header and use the indexes over inv# on detail1 and detail2;
basically pushing the WHERE down into the detail. I've seen the optimizer
do similar things with simpler queries.

I'm wondering if a NTE form would prove more optimize-able...allowing for
index usage on the various detail tables.

Also trying to understand if I need (or want) to use LATERAL on the NTE
form. I've been looking back at articles and archived posts, but I've not
been able to wrap my head around what benefits LATERAL provides.

I did run across a post about Oracle that mentions it will optimize a LEFT
OUTER JOIN of an NTE into a LATERAL; perhaps DB2 does the same and that
explains why I don't see any benefits.

I'd be nice if I could do this as a UTDF, being able to pass the dates in
as variables would make it easy to push down the filtering to the lower
levels. Unfortunately that's not an option.

Thoughts?

Charles