RE: SQL Index builds

X3 on using ACS and Visual Explain as Vern has suggested. You can see so much information with that tool.

However, I can tell you from experience that creating the EXACT index will not always eliminate a Temporary Hash Table built by the SQE. Sometimes the SQE (erroneously in my opinion) determines that a temporary hash is faster.

I have a case open with IBM right now... the temporary hash takes 18 seconds to create. When the index is used, the entire query takes 1-2 seconds.

Best of luck.

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Sean Courtney
Sent: Monday, January 31, 2022 9:39 AM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: SQL Index builds
Importance: High

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