RE: Unexpected SQL performance behavior -- MIDRANGE-L

Here is the SQL command I am using:

/* gei3015t1 is a file that will be fed to an RPG program to generate the report */
/* the different GESEC* files contain rules and exceptions to apply (Ex: what AUTL applies to what LIB ...) */
INSERT INTO dr.gei3015t1 (odlbnm, odobnm, odobtp, odobat, odobtx, odobow, pubaut, autl, adpaut, prvaut)
SELECT n1.objlib, n1.objname, n1.objtype, COALESCE (n1.objattribute,' '), COALESCE (n1.objtext, '.'),
/**/
/* Validate the owner of the object. If the value is in accordance with the GESEC* files, then the value is *blank */
CASE
WHEN n2.secown = '*SAME' OR (coalesce(n2.secown, '*DFT') = '*DFT' AND n1.objowner = 'G_OWNER') OR n2.secown = n1.objowner THEN ' '
ELSE n1.objowner
END AS v_owner,
/**/
/* validate the public authority. If ok then the value is *blank */
CASE
WHEN n4.secraut IS NOT NULL THEN CASE WHEN (n4.secraut = '*DFT' AND n7.obj_auth = '*USE') OR n7.obj_auth = n4.secraut THEN ' ' ELSE n7.obj_auth END
WHEN n2.secautpub IS NOT NULL THEN CASE WHEN (n2.secautpub = '*DFT' AND n7.obj_auth = '*USE') OR n7.obj_auth = n2.secautpub THEN ' ' ELSE n7.obj_auth END
WHEN n6.seclautpub IS NOT NULL THEN CASE WHEN (n6.seclautpub = '*DFT' AND n7.obj_auth = '*USE') OR n7.obj_auth = n6.seclautpub THEN ' ' ELSE n7.obj_auth END
WHEN n5.seclautpub IS NOT NULL THEN CASE WHEN (n5.seclautpub = '*DFT' AND n7.obj_auth = '*USE') OR n7.obj_auth = n5.seclautpub THEN ' ' ELSE n7.obj_auth END
WHEN n7.obj_auth <> '*USE' THEN n7.obj_auth
ELSE ' '
END AS v_pubaut,
/**/
/* validate the autorisation list. If ok then *blank */
CASE
WHEN n3.secaautlst IS NOT NULL THEN CASE
WHEN n3.secaautlst <> '*NONE' AND n8.auth_list IS NULL then '*NONE'
WHEN n3.secaautlst <> '*NONE' AND n8.auth_list IS NOT NULL AND n3.secaautlst <> n8.auth_list then n8.auth_list
WHEN n3.secaautlst <> '*NONE' AND n8.auth_list IS NOT NULL AND n3.secaautlst = n8.auth_list then ' '
WHEN n3.secaautlst = '*NONE' AND n8.auth_list IS NOT NULL then n8.auth_list
WHEN n3.secaautlst = '*NONE' AND n8.auth_list IS NULL then ' '
ELSE '??' END
WHEN n2.secautlst IS NOT NULL THEN CASE
WHEN n2.secautlst <> '*NONE' AND n8.auth_list IS NULL then '*NONE'
WHEN n2.secautlst <> '*NONE' AND n8.auth_list IS NOT NULL AND n2.secautlst <> n8.auth_list then n8.auth_list
WHEN n2.secautlst <> '*NONE' AND n8.auth_list IS NOT NULL AND n2.secautlst = n8.auth_list then ' '
WHEN n2.secautlst = '*NONE' AND n8.auth_list IS NOT NULL then n8.auth_list
WHEN n2.secautlst = '*NONE' AND n8.auth_list IS NULL then ' '
WHEN n2.secautlst = '*SAME' THEN ' '
ELSE '??' END
WHEN n6.seclautlst IS NOT NULL THEN CASE
WHEN n6.seclautlst <> '*NONE' AND n8.auth_list IS NULL then '*NONE'
WHEN n6.seclautlst <> '*NONE' AND n8.auth_list IS NOT NULL AND n6.seclautlst <> n8.auth_list then n8.auth_list
WHEN n6.seclautlst <> '*NONE' AND n8.auth_list IS NOT NULL AND n6.seclautlst = n8.auth_list then ' '
WHEN n6.seclautlst = '*NONE' AND n8.auth_list IS NOT NULL then n8.auth_list
WHEN n6.seclautlst = '*NONE' AND n8.auth_list IS NULL then ' '
ELSE '??' END
WHEN n5.seclautlst IS NOT NULL THEN CASE
WHEN n5.seclautlst <> '*NONE' AND n8.auth_list IS NULL then '*NONE'
WHEN n5.seclautlst <> '*NONE' AND n8.auth_list IS NOT NULL AND n5.seclautlst <> n8.auth_list then n8.auth_list
WHEN n5.seclautlst <> '*NONE' AND n8.auth_list IS NOT NULL AND n5.seclautlst = n8.auth_list then ' '
WHEN n5.seclautlst = '*NONE' AND n8.auth_list IS NOT NULL then n8.auth_list
WHEN n5.seclautlst = '*NONE' AND n8.auth_list IS NULL then ' '
ELSE '??' END
ELSE COALESCE (n8.auth_list, ' ')
END AS v_autl,
/**/
/* validate the adoption of authority. If ok then *blank */
CASE
WHEN n9.usrprf IS NULL THEN ' '
WHEN n3.secaadpaut IS NOT NULL AND n3.secaadpaut <> n9.usrprf then n9.usrprf
WHEN n3.secaadpaut IS NOT NULL AND n3.secaadpaut = n9.usrprf then ' '
WHEN n9.usrprf <> '*OWNER' THEN n9.usrprf
ELSE ' '
END AS v_adpaut,
/**/
/* validate if the object's private authority is the same as those defined in the GESEC* file */
COALESCE (
(SELECT '*' FROM cdtasys.gesecrp q1
WHERE q1.secrusr <> '*PUBLIC' AND q1.secrlib = n1.objlib AND q1.secrobj = n1.objname AND q1.secrtyp = n1.objtype AND
NOT EXISTS(SELECT * FROM qsys2.object_privileges q2
WHERE q2.sys_dname = q1.secrlib AND q2.sys_oname = q1.secrobj AND q2.objtype = q1.secrtyp AND q2.user_name = q1.secrusr AND q2.obj_auth = q1.secraut )
FETCH FIRST ROW ONLY ),
(SELECT '*' FROM qsys2.object_privileges q3
WHERE q3.user_name <> '*PUBLIC' AND q3.user_name <> 'G_OWNER' AND q3.sys_dname = n1.objlib AND q3.sys_oname = n1.objname AND q3.objtype = n1.objtype AND
NOT EXISTS(SELECT * FROM cdtasys.gesecrp q4
WHERE q3.sys_dname = q4.secrlib AND q3.sys_oname = q4.secrobj AND q3.objtype = q4.secrtyp AND q3.user_name = q4.secrusr AND q3.obj_auth = q4.secraut )
FETCH FIRST ROW ONLY ), ' ') AS v_prvaut
/**/
FROM TABLE (qsys2.object_statistics('DRTEST','ALL')) n1 /* for test purposes, I use the DRTEST library. Once in production, I will use *ALL */
LEFT JOIN cgef.gesecpcs n2 ON n1.objtype = n2.sectyp AND (n1.objattribute = n2.secatr OR n2.secatr = ' ')
LEFT JOIN cdtasys.gesecap n3 ON n1.objlib = n3.secalib AND n1.objname = n3.secaobj AND n1.objtype = n3.secatyp
LEFT JOIN cdtasys.gesecrp n4 ON n1.objlib = n4.secrlib AND n1.objname = n4.secrobj AND n1.objtype = n4.secrtyp AND n4.secrusr = '*PUBLIC'
LEFT JOIN cdtasys.geseclp n5 ON n1.objlib = n5.secllib AND n5.seclapp = ' '
LEFT JOIN cdtasys.geseclp n6 ON n1.objlib = n6.secllib AND substring(n1.objname,1,2) = n6.seclapp
LEFT JOIN qsys2.object_privileges n7 ON n1.objlib = n7.sys_dname AND n1.objname = n7.sys_oname AND n1.objtype = n7.object_type AND n7.user_name = '*PUBLIC'
LEFT JOIN qsys2.authorization_list_info n8 ON n1.objlib = n8.sys_dname AND n1.objname = n8.sys_oname AND n1.objtype = n8.sys_otype
LEFT JOIN qsys2.pgm_info n9 ON n1.objlib = n9.pgm_lib AND n1.objname = n9.pgm_name AND n1.objtype = n9.obj_type
WHERE n5.seclyn = '001'
FETCH FIRST 100000 ROWS ONLY;
/**/
/* I will put here a second statement to delete from GEI3015T1 all rows that have no discrepancy (where all v_* variable are *blank) because I can not use the v_* variable in the where clause */

-----Message d'origine-----
De : MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> De la part de Rob Berendt
Envoyé : 2 mars 2022 08:57
À : Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Objet : RE: Unexpected SQL performance behavior

Cascades - External Email

Any chance you can share that statement? IDK if any of IBM's services would perform better. Or may be structured better.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
https://can01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.dekko.com%2F&data=04%7C01%7Cdenis_robitaille%40cascades.com%7C71bbeaee68a246cb32ec08d9fc5494bc%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637818262482133803%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=vnGPkNabpddfPfDnWGruHmNO9dyI5wIsKW2%2BtbjM8eg%3D&reserved=0

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Denis Robitaille
Sent: Wednesday, March 2, 2022 8:38 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Unexpected SQL performance behavior

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Thanks for all these details,

When I added "fetch first 1000 rows only" (found that on google) it performed very well.

The problem I face now is that I currently only have 51 rows because I am working on a subset of data for development purposes. But, once in production, the number of rows will most likely be over 100,000 rows.

To give context, the goal of this is to analyse all objects on a system to see if they conform to our security policies (public authority, owner, authorisation list, adopt authority for pgm ...) and report any non complying object.

-----Message d'origine-----
De : MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> De la part de Charles Wilt Envoyé : 1 mars 2022 18:24 À : Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx> Objet : Re: Unexpected SQL performance behavior

Cascades - External Email

This is likely cause of the difference...
Attribute Select only With insert
Optimization goal *FIRSTIO *ALLIO

Which is what I suspected. :)
*FIRSTIO is the default for Inactive queries, meaning give me the first page ASAP.
*ALLIO is the default for "batch", meaning try to limit the time it takes for all the rows.

Usually a big difference, means that the optimizer doesn't have a good idea of how many rows "ALL" will end up with.
*FIRSTIO tends to favor index usage, *ALLIO tends to favor table scans if it assumes "ALL" is pretty big.

With the right set of indexes and statistics, it should work the same in this case with such few rows.
You could determine what's missing and add them, or you can give it a OPTIMIZE FOR 60 ROWS hint.

Note that adding such a hint may (will) hurt if it turns out you've lied to the optimizer and returned much more than 60 rows.

Yes, add the optimize for clause to the select.
INSERT INTO MYTABLE
(select <...>
optimize for 60 rows
)

Charles

On Tue, Mar 1, 2022 at 2:11 PM Denis Robitaille < denis_robitaille@xxxxxxxxxxxx> wrote:

Using the visual explain, I compared the result when doing the INSERT
with only doing the SELECT.

Attribute Select only With insert
Optimization goal *FIRSTIO *ALLIO
Interactive Process NO NO
Positionning option Next only Next only
Optimize for N rows 30 All lines
Fetch first N rows All rows All rows

I dont know why some values are different.

As for the "optimize for 60 rows" where should I add it (at the end of
the SQL statement?

Thanks
-----Message d'origine-----
De : MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> De la part de
Charles Wilt Envoyé : 1 mars 2022 11:45 À : Midrange Systems Technical
Discussion <midrange-l@xxxxxxxxxxxxxxxxxx> Objet : Re: Unexpected SQL
performance behavior

Cascades - External Email

What does Visual Explain show?

In particular
Under INI Options
Optimization goal *FIRSTIO
Under Environment Information
Interactive Process No
Positioning Options Any
Optimize for N Rows 30
Fetch First N Rows All Rows
Adding
optimize for 60 rows
to the select may help

May help...

Charles

On Tue, Mar 1, 2022 at 8:59 AM Denis Robitaille <
denis_robitaille@xxxxxxxxxxxx> wrote:

Hello all,

I am currently working on a complex (for me) SQL script. When I run
it to display the result on the screen, it works fine. When I change
it to put the result of the SELECT in a file (by inserting the
appropriate INSERT INTO... before the SELECT), it takes more that 10

times longer to execute!!

I am working with a small subset of data and the resulting data set
is just 51 rows.

Any help on understanding this behavior and on how to avoid it is
very appreciated.

[cid:image001.png@01D82D5A.F658C930]<http://www.cascades.com/fr>
Denis Robitaille
Analyste TI
CASCADES CANADA ULC
412 Boul. Marie-Victorin
Kingsey Falls (QC) Canada J0A 1B0
Tél. : 819 363 6100 Poste : 52130
Cell. : 819 352 9362

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://can01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fli
st
s.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=04%7C01%7C
de
nis_robitaille%40cascades.com%7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca
86
6874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817499189711805%7CUnknown
%7
CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJ
XV
CI6Mn0%3D%7C3000&sdata=KTQ6duWhZeG843okLQ%2FTFr%2Fd7Xg4VO%2BB8%2
BY
yyVFyNN8%3D&reserved=0 or email:
MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at

https://can01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farch
ive.midrange.com%2Fmidrange-l&data=04%7C01%7Cdenis_robitaille%40ca
scades.com%7Cdb49ec6c22034ec3ed8a08d9fbda9046%7Ca866874ad0e34a03a79d4c
893ab51296%7C0%7C0%7C637817739350298812%7CUnknown%7CTWFpbGZsb3d8eyJWIj
oiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&am
p;sdata=lMjasGZE6fi3%2BD%2F2cEQt%2FEYR52s0q4YI7aI52qDANFU%3D&reser
ved=0
.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link:
https://can01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fam
az
on.midrange.com%2F&data=04%7C01%7Cdenis_robitaille%40cascades.co
m%
7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4c893ab5129
6%
7C0%7C0%7C637817499189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjA
wM
DAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=t
Sj
8Jfjbk62sQGv%2Ba9cHSsHQrg1AqbMAseoywVgJ3p0%3D&reserved=0

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit:
https://can01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flist
s.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=04%7C01%7Cde
nis_robitaille%40cascades.com%7Cdb49ec6c22034ec3ed8a08d9fbda9046%7Ca86
6874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817739350298812%7CUnknown%7
CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXV
CI6Mn0%3D%7C2000&sdata=K6YFPjI%2FE4jMW%2FQazbEq67f5UTZCKjpzXKDj7L9
9EoE%3D&reserved=0 or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://can01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farch
ive.midrange.com%2Fmidrange-l&data=04%7C01%7Cdenis_robitaille%40ca
scades.com%7Cdb49ec6c22034ec3ed8a08d9fbda9046%7Ca866874ad0e34a03a79d4c
893ab51296%7C0%7C0%7C637817739350298812%7CUnknown%7CTWFpbGZsb3d8eyJWIj
oiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&am
p;sdata=lMjasGZE6fi3%2BD%2F2cEQt%2FEYR52s0q4YI7aI52qDANFU%3D&reser
ved=0
.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://can01.safelinks.protection.outlook.com/?url=https%3A%2F%2Famaz
on.midrange.com%2F&data=04%7C01%7Cdenis_robitaille%40cascades.com%
7Cdb49ec6c22034ec3ed8a08d9fbda9046%7Ca866874ad0e34a03a79d4c893ab51296%
7C0%7C0%7C637817739350298812%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwM
DAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=QEk
wRyPw9%2FZ1F%2FdOv%2FVsrRmFdhFkZisotTalkzXQgbQ%3D&reserved=0

Cascades - ATTENTION: Ce courriel provient de l'extérieur de
l'organisation. Ne pas cliquer sur les liens et ne pas ouvrir les
pièces jointes sauf si vous reconnaissez l'expéditeur et que vous êtes
sûr que le contenu est légitime.
Cascades - CAUTION: This email is from outside the organization. Do
not click on links or open attachments unless you recognize the sender
and you are sure the content is safe.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit:
https://can01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flist
s.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=04%7C01%7Cde
nis_robitaille%40cascades.com%7Cdb49ec6c22034ec3ed8a08d9fbda9046%7Ca86
6874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817739350298812%7CUnknown%7
CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXV
CI6Mn0%3D%7C2000&sdata=K6YFPjI%2FE4jMW%2FQazbEq67f5UTZCKjpzXKDj7L9
9EoE%3D&reserved=0 or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://can01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=04%7C01%7Cdenis_robitaille%40cascades.com%7C71bbeaee68a246cb32ec08d9fc5494bc%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637818262482133803%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=0XdfrdM2vwUBnBWdqr8dXzJo9wnI0pELviq11Rq72zU%3D&reserved=0.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://can01.safelinks.protection.outlook.com/?url=https%3A%2F%2Famaz
on.midrange.com%2F&data=04%7C01%7Cdenis_robitaille%40cascades.com%
7Cdb49ec6c22034ec3ed8a08d9fbda9046%7Ca866874ad0e34a03a79d4c893ab51296%
7C0%7C0%7C637817739350298812%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwM
DAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=QEk
wRyPw9%2FZ1F%2FdOv%2FVsrRmFdhFkZisotTalkzXQgbQ%3D&reserved=0