You are using a different optimization goals.
*FISTIO means optimize so the first block of data is returned as fast as
possible, i.e. if the query optimizer has to decide between a sub-optimal
index and a table scan it may use an index access.
*ALLIO means optimize so the complete SQL statement is executed as fast as
possible, i.e. if the query optimizer has to decide between a sub-optimal
index and a table scan it may use the table scan.
The optimization goal can be affected by adding a OPTIMIZE FOR X ROWS clause
to the SELECT-Statement.
When replacing the X with a small integer, the optimization goal *FIRSTIO is
used.
When replacing the x with a large integer or ALL the optimization goal
*ALLIO is used.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Denis
Robitaille
Sent: Dienstag, 1. März 2022 22:11
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Unexpected SQL performance behavior
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%2Flist
s.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=04%7C01%7Cde
nis_robitaille%40cascades.com%7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca86
6874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817499189711805%7CUnknown%7
CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXV
CI6Mn0%3D%7C3000&sdata=KTQ6duWhZeG843okLQ%2FTFr%2Fd7Xg4VO%2BB8%2BY
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%2Farchive.mi
drange.com%2Fmidrange-l&data=04%7C01%7Cdenis_robitaille%40cascades.com%7
C06f5740c4af5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0
%7C637817499189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2
luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=egNm9R2qsyH9I6hXe1OmYQ
zLL19vgBejrqAHIAhe9AU%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%
7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4c893ab51296%
7C0%7C0%7C637817499189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwM
DAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=tSj
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%2Flists.midr
ange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=04%7C01%7Cdenis_robitail
le%40cascades.com%7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4
c893ab51296%7C0%7C0%7C637817499189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4
wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=KTQ
6duWhZeG843okLQ%2FTFr%2Fd7Xg4VO%2BB8%2BYyyVFyNN8%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.mi
drange.com%2Fmidrange-l&data=04%7C01%7Cdenis_robitaille%40cascades.com%7
C06f5740c4af5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0
%7C637817499189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2
luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=egNm9R2qsyH9I6hXe1OmYQ
zLL19vgBejrqAHIAhe9AU%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.mid
range.com%2F&data=04%7C01%7Cdenis_robitaille%40cascades.com%7C06f5740c4a
f5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C63781749
9189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBT
iI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=tSj8Jfjbk62sQGv%2Ba9cHSsHQrg1AqbM
AseoywVgJ3p0%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://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
