Re: Unexpected SQL performance behavior -- MIDRANGE-L

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%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.midrange.com%2Fmidrange-l&data=04%7C01%7Cdenis_robitaille%40cascades.com%7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817499189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=egNm9R2qsyH9I6hXe1OmYQzLL19vgBejrqAHIAhe9AU%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.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=04%7C01%7Cdenis_robitaille%40cascades.com%7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817499189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=KTQ6duWhZeG843okLQ%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.midrange.com%2Fmidrange-l&data=04%7C01%7Cdenis_robitaille%40cascades.com%7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817499189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=egNm9R2qsyH9I6hXe1OmYQzLL19vgBejrqAHIAhe9AU%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%7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817499189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=tSj8Jfjbk62sQGv%2Ba9cHSsHQrg1AqbMAseoywVgJ3p0%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