Re: SQL SELECT RETRIEIVAL TIME

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

It could also be affected by the time needed to load the data itself, as 
well as indexes, an also all the components of the query engine into 
memory. I assume there is a lot happening at night that would have purged 
all this out. You could try scheduling a job to run periodically, to 
execute this statement. Also, if you have sufficient memory to dedicate to 
this, you could use SETOBJACC on the main data files and the indexes, into 
a fixed pool dedicated to this. This pool should have no jobs run in it. At 
end of day you could SETOBJACC *PURGE and resize the pool, in order to 
allow other processes to work better.

I might start with the indexes, since it appears the IO is random access, 
not sequential. Esp. if the parent physical is quite large. And esp. if 
there is opportunity for index-only access to data.

HTH

Vern

At 11:07 AM 2/25/2003 -0500, you wrote:
> 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