Re: SQL CTE vs NTE (with LATERAL?)

Hi Charles,

I think your tweak should produce the same results.

In my mind, the primary benefit of LATERAL joins are flexibility. They
allow you to reference columns produced earlier in the result set inside
the lateral query.

I work with a lot of data where I need to pick one row (a best match row)
out of many, and you can embed an ORDER BY with a FETCH FIRST ROW ONLY
inside that lateral query, to accomplish just that. I tend to use them a
lot when working with date ranges.

In short, I think they're a great building block to simplify more complex
tasks, especially when the data is difficult to work with.

Given the SQL engine essentially rewrites our queries, we're at its mercy
to a significant degree in terms of performance.

Try both, and use whichever one performs best. I commonly write multiple
versions of a query, and typically use whichever one performs best. If I
write 2 or 3 versions of a query that all perform bad, then I start digging
into Visual Explain, but the first thing I do there is look at advised
indexes.

Keep in mind that you can reference global variables inside a view now. So,
you can use those to get the filter date range in effect for the view.
That is a bit easier than using a UDTF. A view will likely perform better
than a UDTF in cases where the view needs to be joined to something else.
In general, the earlier you filter out row discards, the better your query
performs.

Mike

date: Tue, 18 Apr 2017 11:30:56 -0600
from: Charles Wilt <charles.wilt@xxxxxxxxx>
subject: Re: SQL CTE vs NTE (with LATERAL?)

Mike,

Seems pretty equivalent to
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 (
select d1.inv#, d1.linetype
, sum(d1.colA) as col1A_tot
, sum(d1.colB) as col1B_tot
from detail1 d1
group by d1.inv#, d1.linetype
) as d1 on d1.inv# = h.inv#

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

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

What's the point of the LATERAL?

Charles

On Tue, Apr 18, 2017 at 11:13 AM, Mike Jones <mike.jones.sysdev@xxxxxxxxx>
wrote:

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