1. Writing physically into a temp table is (relatively) slow!
2. Generating indexes on what ever table is also slow, because for creating
an index multiple tables scans might be used.
3. there are only index advices for permanent tables
4. I suspect that your are using dynamic SQL. If the statements are built
dynamically the optimizer has to run after each prepare through a full open,
so reusing open data paths are not possible, another factor that slows down
your queries
Normally individually created temporary tables are not necessary, with the
right syntax and the right indexes (but there might be exceptions)
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
IBM Champion since 2020
"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)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
DEnglander--- via MIDRANGE-L
Sent: Tuesday, 21 November 2023 15:50
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: DEnglander@xxxxxxxxxxxxxxx
Subject: Index Advisor Question
I have an RPG program with embedded SQL that interacts with a DSPF. The DSPF
gives the user a screen where they can filter the data sent to the DSPF [a
subfile]. Because of the amount of filters and logic that this screen
requires for it to be built, I have used a SQL Insert to a QTEMP table with
a small "update" subroutine to add data to three more columns [after the
Insert] before it writes out to the screen, sorting and filtering as the
User requested.
This Insert goes to a QTEMP table. I would like to see the Index Advisor
recommendations for this table to get SQL to run as fast as possible. Is
there a way for the Index Advisor to give results for a QTEMP table? An
alternative to the QTEMP table might be to use a DECLARE GLOBAL TEMPORARY
table. But again, would this be available to Index Advisor?
The reason I have not made this Insert/Filter/Sort routine all SQL is
because if I did that, the SQL statement would span three pages and would be
difficult to maintain. I am trying to break into steps/sections for easier
maintenance, but also value the input from the Index Advisor to make this
screen load as fast as possible. I have already made it a lot faster than
previously, but was wondering if any more indexes would help it.
Any ideas?
Thank you,
Doug
"CONFIDENTIALITY NOTICE: This e-mail transmission (and/or the attachments
accompanying it) contain confidential information belonging to the sender.
The information is intended only for the use of the intended recipient. If
you are not the intended recipient, you are hereby notified that any
disclosure, copying, distribution or the taking of any action in reliance on
the contents of the information is strictly prohibited. Any unauthorized
interception of this transmission is illegal under the law. If you have
received this transmission in error, please promptly notify the sender by
reply e-mail, and then destroy all copies of the transmission."
--
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.
midrange.com
