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&amp;data=04%7C01%7Cde
nis_robitaille%40cascades.com%7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca86
6874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817499189711805%7CUnknown%7
CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXV
CI6Mn0%3D%7C3000&amp;sdata=KTQ6duWhZeG843okLQ%2FTFr%2Fd7Xg4VO%2BB8%2BY
yyVFyNN8%3D&amp;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&amp;data=04%7C01%7Cdenis_robitaille%40cascades.com%7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817499189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&amp;sdata=egNm9R2qsyH9I6hXe1OmYQzLL19vgBejrqAHIAhe9AU%3D&amp;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&amp;data=04%7C01%7Cdenis_robitaille%40cascades.com%
7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4c893ab51296%
7C0%7C0%7C637817499189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwM
DAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&amp;sdata=tSj
8Jfjbk62sQGv%2Ba9cHSsHQrg1AqbMAseoywVgJ3p0%3D&amp;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&amp;data=04%7C01%7Cdenis_robitaille%40cascades.com%7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817499189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&amp;sdata=KTQ6duWhZeG843okLQ%2FTFr%2Fd7Xg4VO%2BB8%2BYyyVFyNN8%3D&amp;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&amp;data=04%7C01%7Cdenis_robitaille%40cascades.com%7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817499189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&amp;sdata=egNm9R2qsyH9I6hXe1OmYQzLL19vgBejrqAHIAhe9AU%3D&amp;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&amp;data=04%7C01%7Cdenis_robitaille%40cascades.com%7C06f5740c4af5442b75ff08d9fba2cd3d%7Ca866874ad0e34a03a79d4c893ab51296%7C0%7C0%7C637817499189711805%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&amp;sdata=tSj8Jfjbk62sQGv%2Ba9cHSsHQrg1AqbMAseoywVgJ3p0%3D&amp;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.