Re: SQL Index builds

Hi,

I can only support Verns suggestion - iACS Run SQL Scripts and Visual Explain are really good.

Just look into suggested indexes and check, which indexes are suggested - often VE suggested multiple indexes, but in most cases, you only need one.

And ... you have written, you "create new LF's" which sounds like DDS-LFs - and from my personal experience, not every DDS-LF makes a good SQL index - so if you can, relate your indexes with CREATE INDEX ... not with DDS.

HTH
Daniel
Am 31. Jan. 2022, 15:56 +0100 schrieb Vern Hamberg via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>:

This can give some information. Best is to use a combination of the
tools in Access Client Solutions, as others have suggested a couple of.

In ACS is the SQL Performance Center - you can see SQL statements that
were used in jobs - then you can run Visual Explain over the statement,
VE will give you ever so much more information than STRDBG can. And
Index Adviser was suggested - you should not automatically create them
all, every index you create means more time to execute database I/O,
since the index has to be updated for inserts, writes, updates, deletes,
all that.

If you don't have ACS, many of the tools are available in the previous
Client Access or Access for IBM i, in Navigator there you will see
several actions you can take, in a section near the bottom.

HTH
Vern

On 1/31/2022 8:40 AM, Peter Colpaert wrote:

I think you can run your program in debug (STRDBG) and then view the temp
indexes in your joblog.
--

Peter Colpaert

Peter.Colpaert@xxxxxxxxx


Op ma 31 jan. 2022 om 15:39 schreef Sean Courtney <scourtney@xxxxxxxxxxx>:

Hello everyone,



maybe someone else also had similar issues.



Sometimes when I join tables in SQL a temporary index is built during
runtime.



I tried to counteract this by adding new LF’s and indexes … sometimes it
works and sometimes it doesn’t…



Is there a way to find out how sql builds these indexes ??



If yes could someone please give we a quick update on how to find the index
definition. If I were to create these indexes permanently then I would
probably

Improve runtime performance considerably ..







Mit freundlichen Grüßen / Kind regards / Bien à vous,



Sean

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-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

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-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