RE: SQL SELECT RETRIEIVAL TIME

["Chevalier, Rick" <Rick.Chevalier@xxxxxxxxxxxxxxx>]

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.