Re: SQL SELECT RETRIEIVAL TIME -- MIDRANGE-L

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

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.