Vern, Thanks a lot for big reply.
---------
Now I have a program I wrote originally with SQL, chosen over native
IO for various reasons that seemed good at the time. It has turned
out to be a royal dog in performance. I just replaced it with a new
logical - one not in our product but over a file IN our product - and
the runtime of that program went down by a factor of 50. And there
WERE indexes for each of the components of the INSERT based on a
subSELECT, yet they did not help. It would go from 50 seconds or more
to even subsecond for some of these jobs.
--------
Refer to above phrase, can you explain me in detail how the performance (run time) improved by a factor of 50 and what were the reasons ?
Vernon Hamberg <vhamberg@xxxxxxxxxxx> wrote:
Donna
SQL performance depends heavily on the existence of appropriate
indexes, so that the optimizer can choose the most efficient method
of processing the SQL statement, whatever it is. SQL is like a black
box - you sometimes have to guess what to feed Schroedinger's Cat
(sorry!!) and almost as predictable as quantum physics.
There are no demerits to having indexes - except if they are not used
and end up wasting access path maintenance. What you call keys - the
more usual term is indexes - are just keyed LFs. Our familiar LFs
created from DDS source are such indexes, as well as indexes created
using the "create index" SQL statement. And constraints on a PF also
are eligible as indexes for the optimizer to use.
There is a manual that you really should read - it is under Database
in infocenter and has a title something like SQL performance and
optimization. The name changes from release to release - just like
the system! It has extensive information on the various ways indexes
are used by the SQL engines.
Indexes also provide statistical information that the optimizer can
use, even if it does not use it for actually reading the file.
One thing - the optimizer almost never uses an LF that you specify in
a SELECT statement - only exception I know is if you name a
select/omit logical. So it is never necessary - almost never - to
specify a logical in a SELECT in order to "help" the optimizer choose
an appropriate access plan - in fact, it often does NOT use that
logical - it finds, as far as I know, the PF over which the logical
is built and proceeds from there to look at all the other LFs
available. At least something like that.
You can get more insight by simply executing STRDBG - no program
specified - and running a SELECT statement - you will see optimizer
messages in your job log that tell you what it did to figure out the
best access plan.
Now I have a program I wrote originally with SQL, chosen over native
IO for various reasons that seemed good at the time. It has turned
out to be a royal dog in performance. I just replaced it with a new
logical - one not in our product but over a file IN our product - and
the runtime of that program went down by a factor of 50. And there
WERE indexes for each of the components of the INSERT based on a
subSELECT, yet they did not help. It would go from 50 seconds or more
to even subsecond for some of these jobs.
So SQL is not always the best way to go - and the time to MAKE it the
best - not worth it here when I have an option that is so easy to
implement. And that is another thread - about REAL RDBMS' - that has
been running here of late!
Hope this makes a little clearer the topic!
Vern
At 04:38 PM 3/12/2008, you wrote:
Hi
I heard the performance of the programs improve with new SQL
keys.The performance I meant is whether the programs run faster
with new SQL keys.
I would like to find out merits&demerits of having SQL keys from
anyone who has experience using them.
I thank you all who responded on my MQ question, your information
was very helpful.
Thanks,
Donna.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
midrange.com
