RE: SQL query optimization -- RPG400-L

Mike,

Since you mentioned you're doing this for a sfl pgm -

Here's something that got me a couple times.  If I
don't specify ALWCPYDTA = *NO and ALWBLK = *NONE, then
the cursor isn't always sensitive to database updates.

Also, by specifying ALWBLK = *NO, the help text for
CRTSQLRPGI says there are performance advantages for
the first few rows which is what you're concerned with
with a sfl pgm.

The other thing I do is include an 'optimize for'
clause on the select statement.

FWIW.

Phil


--- "Smith, Mike" <Mike_Smith@RGCResources.com> wrote:
> 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.
> _______________________________________________
> 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.
>


__________________________________________________
Do You Yahoo!?
Send FREE Valentine eCards with Yahoo! Greetings!
http://greetings.yahoo.com