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
---------------------------------------------------------------------
midrange.com
