Re: SQL keys

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.