Re: SQL SELECT RETRIEIVAL TIME

["Jim Franz" <franz400@xxxxxxxxxxxx>]

Are any of the view/logical files access path not updated *immed?
I have a similar app. I have found also that if sysval qpfradj-performance
adjust is on, 1st thing in morning the memory pools still adjusted for
mostly batch processing at night.
jim

----- Original Message -----
From: "Chevalier, Rick" <Rick.Chevalier@xxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, February 27, 2003 11:27 AM
Subject: RE: SQL SELECT RETRIEIVAL TIME


> Dave,
>
> I think the issue might be related to the view you mentioned.  When the
view
> is accessed the underlying SQL statement is executed before the view is
> available for use.  I recently created view here and it does take a little
> while to access even when using utilities like DBU.
>
> Try opening the view using DBU, DFU, Query or anything other than the
> program in question prior to the first user executing a selection in your
> program.  After the view is open then perform the first selection.  If it
is
> the view, the selection should perform 'almost immediate'.
>
> Hth,
>
> Rick
>
> -----Original Message-----
> From: Smith, Dave [mailto:DSmith@xxxxxxxxx]
> Sent: Tuesday, February 25, 2003 10:08 AM
> To: midrange-l@xxxxxxxxxxxx
> Subject: SQL SELECT RETRIEIVAL TIME
>
>
> I recently converted a RPGLE program that was reading through several
files
> to produce a subfile like screen to present information to the user.   I
> converted this program to use embedded SQL.  In a nutshell, the user
enters
> any combination of Item, Item Group, and/or Buyer to display.  The program
> dynamically builds the SQL statement, PREPAREs the statement, and loads
the
> CURSOR with the result set.  This program is used every day (all day) by
> about 50 different users.  It appears that the first person in each day to
> use this program has a 15-20 second wait time for the SELECT  to execute.
> All access by all users (including the original user) after that time is
> almost immediate regardless if the selection criteria changes.   I'm
> assuming the query optimizer is doing something that first time that is
> affecting the retrieval time.  What I want to know, is what can be done so
> it does not have to re-do this every day the first time in?  This is not a
> huge issue, but more a cur!
> iosity.
>
> We are running V5R1.  I have run all the various combinations of the
SELECT
> statement via the Run and Explain option in iSeries Navigator and created
> all the suggested indexes.  There is one View involved in the select
> statement that is joining two files.  I do not see any new access paths
> being created at run time.  Any ideas would be appreciated.  Thanks!
>
> David Smith
> IT Consultant
> dsmith@xxxxxxxxx
> 517.546.5400
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>