RE: SQL query optimization -- RPG400-L

thanks for the info John
its interesting.

i've changed the compile to force it to use the logical.  WHY you ask, well
i'll tell you.
First, there is only 1 logical.  1 keyfield on the file.  THe select
statement uses the keyfield as the order by field.  There are about 500,000
records in the file.

the whole purpose of this is doing a subfile using SQL.  when i let the
optimizer decide on its own, it takes 5-6 seconds to display the first
subfile page.  When i force it to use the access path, i get near
instantaneous display of the first subfile page.
To me and my tiny brain it seems like that using the access path of the
logical is the correct way to go.

as far as memory and type of dasd, i'm not sure what those numbers are off
hand, but comparing the 2 methods, the access path method, wins hands down,
but optimizer didn't choose it.

-----Original Message-----
From: jpcarr@tredegar.com [mailto:jpcarr@tredegar.com]
Sent: Thursday, February 14, 2002 8:53 AM
To: rpg400-l@midrange.com
Subject: Re: SQL query optimization



Mike

Lots of possibilities.    How about;

you may have 30 LF's over the file and the optimizer gave up looking after
about 8 - 10 and did the sort. (BTW,  it inspects them Chronologically.
Last one created, first one looked at.   If your most useful LF indexes
were the ones you  created first,  the optimizer may never find them
(especially if you have 20,30,50.....100 LF's over the file)

It may have taken more time for it to bring the index into memory than do
the sort.   Remember if the file has less than (say) 20,000 records or so,
the optimizer probably NEVER will use any  index.   It can do a DB scan
over the whole file faster than anything else.

It looks at available Memory size,   Processor number(speed),  are you
running SMP,   What type of DASD you are running,  and ALL kinds of stuff.
It's really sweet.   Imagine,  Adding more memory, or different disk type
or processor upgrade and the Optimizer automatically recognizing that
change and the next time your ad hoc query runs it will know that fact.

Use PRTSQLINF on the program object and see what you see on the spool file.

Many many times  people's "Tuning"  to second guess the optimizer be the
equivalent of an axe against a chainsaw.

Let it do the work.   Pay attention to the creation dates of the LF's,
Watch your Where statement(that statement usually dictates which index if
any you are using,   NOT the Order By statement BTW).

How many records are in the File?   How many LF's are over the file would
be my first questions.


John Carr

------------------------------------------------


I have an embedded SQL program that I have noticed a delay on the OPEN
CURSOR.  After running it through debug, i find that it is creating a
'Temporary result file' CPI4325 with reason code of '7' which says that the
optimizer decided to do a sort rather than an access path.
The file has a Key on it, in the order that i have selected on my SQL
statement.  So i thought(ok, it doesn't like the key on the physical.) SO i
created a logical file with the proper key.  I still get the same thing
happening.

Does any one have any ideas.

Why is it not using the access path of my logical?
suggesstions!!

Thanks

Mike

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.