Re: SQL SELECT RETRIEIVAL TIME

["Carel Teijgeler" <coteijgeler@xxxxxxxxx>]

Dave,

The only reason(s) I can think of the long wait at the first use:

1) the system is rebuilding its cache, after either the subsystem QINTER was 
ended normally or there was an IPL in the night; this as a consequence of the 
backup routine in your shop (assuming here).

2) Cleanup cleared the cache.

Any way, the Query optimizer/engine has to be started again.

Just my thoughts.

Regards,
Carel Teijgeler

*********** REPLY SEPARATOR  ***********

On 25-2-03 at 11:07 Smith, Dave 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 curiosity.