... SQL logical files can be analyzed by the SQE since Release 7.1 TR 5.
But you should avoid specifying DDS described logical files.
The Query Optimizer has to rewrite the query (under the covers) based on the
underlying physical files/SQL tables.
Creating and using SQL views (unkeyed logical files) on the other side is no
problem.
BTW specifying an index in a SQL statement is not allowed. But SQL indexes
can be used in composition with native I/O like any keyed DDS described
logical file.
But SQL Indexes are much more powerful.
Just in case you are interested in using SQL Indexes with native I/O
https://developer.ibm.com/technologies/systems/articles/i-sql-indexs-and-nat
ive-io/
But logical files have per default a smaller page size than SQL indexes, so
the query optimizer may prefer SQL indexes over DDS described logical files
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"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)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren
Strong
Sent: Dienstag, 29. Dezember 2020 14:47
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: RPGLE Converted into SQL having performance issue
Your #2 was much improved in later operating system levels. I think I
remember you saying you were at V7R2 maybe? Well, at V7R4, if not before,
you can choose the logical, and still have good performance.
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Singh,
Ashwani (Lake Mary) via RPG400-L
Sent: Monday, December 21, 2020 7:14 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Cc: Singh, Ashwani (Lake Mary) <Ashwani.Singh@xxxxxxxxxx>
Subject: RE: RPGLE Converted into SQL having performance issue
CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know the
content is safe.
Thanks Birgitta,
With all the changes suggested here and few others I was able to get
significant improvement in time.
VE shows huge benefits after making the below changes !!!
1) Changed SQL to just fetch the single column, which was required for my
processing.
2) Changed SQL to use PF instead of logical
Will try to add multiple Fetch logic as well and see how it behaves.
Thanks to all of you amazing people!
--
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
midrange.com
