Re: SQL CTE of a UNION kills performance -- MIDRANGE-L

I've found this to be true as well which is very disappointing.

It would be expected that the use of CTE's would out perform creating non-indexed output files along the way and using them the same as the table expression.

Jim
________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> on behalf of Krill, Coy <CKrill@xxxxxxxxxxx>
Sent: Tuesday, August 28, 2018 1:36 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL CTE of a UNION kills performance

Buck, just had the same problem yesterday on a Power 7+ running 7.3 TR4 DB group 10. I have two CTE queries with 4 or 5 unions in each and normally they run in 10-15 minutes but we had to kill them after an hour of not returning anything. I rewrote them as UDTFs without any unions as we only run them every six months. When I have time in October, I was going to see about engaging IBM through our VAR as I assume it's related to PTFs since it was fine in May.

Coy Krill
Core Processing Team Lead
Washington Trust Bank

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> On Behalf Of Buck Calabro
Sent: 2018 August 28 09:41
To: midrange-l@xxxxxxxxxxxx
Subject: SQL CTE of a UNION kills performance
Importance: Low

7.3 TR4, latest cume, DB group 10, Power 9.

I'm trying to suss out the performance issues in a very complex view, and Visual Explain pointe me to this piece. 4 tables, UNIONed together as a CTE. VE tells me that the UDFs are the culprit, but that makes no sense as the exact same UDFs run in .001 over the exact same tables when I run the 4 UNIONs alone.

This runs in milliseconds:

SELECT 'SOC' FILESOURCE, SOC.LOCNO LOCNO, SOC.SOCSEC SOCSEC,
SOC.FLNM3 || SOC.FLNM24 NAME, SOC.LSTNAM, SOC.STREET, SOC.ADDR2, SOC.CITY, SOC.STATE, SOC.ZIP, SOC.ZIPPT2, SOC.MEMBCD, DONOR, SOC.MEMSIN, CAST(SOC.RFDLT AS CHAR(1)) RFDLT, SOC.NSTMEM, SOC.AFTMEM, SOC.LEVEL, CVTNMDYTONUSA(BDAY, '1') DOB, TELENO, SEX, SENDST, CONDST, SOC.AD, LD, SOC.COUNTY, SDCODE, PRTYRG, REGSTD, RETCNL, CAST(SOC.NMSUFX AS CHAR(10)) NMSUFX, MMDDYY2YYYYMMDD(SOC.DATE) DATE, BLDGNO, COALESCE( (SELECT BLDNAME FROM EMRBUILD WHERE LOCAL = SOC.LOCNO AND BLDNUM = BLDGNO ORDER BY LENGTH(TRIM(BLDNAME)) DESC FETCH FIRST 1 ROWS ONLY), '') BUILDNAME, CURPAY, ELEDST FROM SOCMST SOC

UNION ALL
SELECT 'UFT' FILESOURCE, UFT.LOCNO, UFT.SOCSEC,
UFT.FLNM3 || UFT.FLNM24 NAME, UFT.LSTNAM, UFT.STREET, UFT.NOST2, UFT.CITY, UFT.STATE, UFT.ZIP, UFT.ZIPPT2, UFT.MEMBCD, DONOR, SUBSTR(DIGITS(MEMSIN), 5, 2), CAST(' ' AS CHAR(1)), UFT.NSTMEM, UFT.AFTMEM, UFT.LEVEL, UFT.DOB DOB, TELENO, SEX, UFT.SD, CONDST, UFT.AD, LD, UFT.COUNTY, SDCODE, PRTYRG, REGSTD, '',
CAST(' ' AS CHAR(10)), CVTNUSATONISO(UFT.DATE ),
BLDGNO, '' BUILDNAME, CURPAY, ELEDST
FROM UFTMSTR UFT

UNION ALL
SELECT 'UUP' FILESOURCE, CAMP1 CONCAT CAMP2, UUP.SOCSEC,
UUP.FLNM3 || UUP.FLNM24 NAME, UUP.LSTNAM, UUP.STREET, UUP.ADDR2, UUP.CITY, UUP.STATE, UUP.ZIP, UUP.ZIPPT2, UUP.MEMBCD, DONOR, UUP.MEMSIN, CAST(' ' AS CHAR(1)), UUP.NSTMEM, UUP.AFTMEM, UUP.LEVEL, CVTNMDYTONUSA(UUP.BIRTHD, '1') DOB, TELENO, SEX, DIGITS(SENATE), UUP.CD, DIGITS(UUP.AD), SUBSTR(WARD, 1, 2), UUP.COUNTY, SDCODE, PRTYRG, REGSTD, '',
CAST(' ' AS CHAR(10)), MMDDYY2YYYYMMDD(UUP.TRANDT ),
0, '' BUILDNAME, CURPAY, ED
FROM UUPMSTR UUP
WHERE MEMBCD <> 'C'

UNION ALL
SELECT 'PSC' FILESOURCE, PSC.LOCNO, PSC.SOCSEC,
PSC.FLNM3 || PSC.FLNM24 NAME, PSC.LSTNAM, PSC.STREET, PMST.NOST2, PSC.CITY, PSC.STATE, PSC.ZIP, ZIPPT2, PSC.MEMBCD, DONOR, PSC.MEMSIN, CAST(' ' AS CHAR(1)), PSC.NSTMEM, PSC.AFTMEM, PSC.LEVEL, CVTNMDYTONUSA(PMST.DOB, '1') DOB, PSC.TELENO, PSC.SEX, CASE WHEN HEX(SN) <> '4040' THEN DIGITS(SN) ELSE ' ' END, CONDST, DIGITS(ASMDST), SUBSTR(WARD, 1, 2), PSC.COUNTY, SDCODE, PRTYRG, REGSTD, '',
CAST(' ' AS CHAR(10)), MMDDYY2YYYYMMDD(PSC.DATE ),
BLDGNO, '' BUILDNAME, CURPAY, BLK3
FROM PSCSOC PSC
LEFT JOIN PSCMST PMST ON PSC.SOCSEC = PMST.SOC

Wrapping it in a CTE takes 2 minutes.

with temp as (
-- the exact same 4 UNIONs
)
select *
from temp

Visual Explain is correct in that removing the UDFs does in fact speed the thing up. The question is this: Why does wrapping this an a CTE slow it down?

--
--buck

http://wiki.midrange.com
Your updates make it better!

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD

---------------------------------------------------------------------
This electronic mail message and any attachments may contain confidential or privileged information and is intended for use solely by the above-referenced recipient. Any review, copying, printing, disclosure, distribution, or other use by any other person or entity is strictly prohibited under applicable law. If you are not the named recipient, or believe you have received this message in error, please immediately notify the sender by replying to this message and delete the copy you received

---------------------------------------------------------------------

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD