Re: SQL SELECT RETRIEIVAL TIME

[Myles Toomey <empty@xxxxxxx>]

Answering from complete ingnorance.

When you create your SQLRPGLE program do you have the option set to close 
cursors (on the second page somewhere) to *ENDMOD or *ENDACTGRP? If the former, 
it will drop the cursor(s) when the modules (program) ends. If the latter it 
keeps them 'till the end of the job (in which case they need to be scroll 
cursors, and refresh the data)

Just a thought.

On Tuesday, February 25, 2003, at 10:07AM, Smith, Dave <DSmith@xxxxxxxxx> 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 cu!
r!
>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.
>
>
>
 

Myles Toomey
empty@xxxxxxx