RE: Unexpected SQL performance behavior -- MIDRANGE-L

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%7Cdb49ec6c22034ec3ed8a08d9fbda9046%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817739350298812%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=lMjasGZE6fi3%2BD%2F2cEQt%2FEYR52s0q4YI7aI52qDANFU%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

--
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%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=04%7C01%7Cdenis_robitaille%40cascades.com%7Cdb49ec6c22034ec3ed8a08d9fbda9046%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817739350298812%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=K6YFPjI%2FE4jMW%2FQazbEq67f5UTZCKjpzXKDj7L99EoE%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%7Cdb49ec6c22034ec3ed8a08d9fbda9046%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817739350298812%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=lMjasGZE6fi3%2BD%2F2cEQt%2FEYR52s0q4YI7aI52qDANFU%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%2Famazon.midrange.com%2F&data=04%7C01%7Cdenis_robitaille%40cascades.com%7Cdb49ec6c22034ec3ed8a08d9fbda9046%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817739350298812%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=QEkwRyPw9%2FZ1F%2FdOv%2FVsrRmFdhFkZisotTalkzXQgbQ%3D&reserved=0