× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Try adding the fields in the WHERE clause to be part of the key of the
logical.  That may help, as there is now a sort on those fields.

Michael Schutte
Work 614-492-7419
email  michael_schutte@xxxxxxxxxxxx


                                                                           
             "Kyle Collie"                                                 
             <kcollie@xxxxxxxx                                             
             m>                                                         To 
             Sent by:                  "RPG programming on the AS400 /     
             rpg400-l-bounces@         iSeries" <rpg400-l@xxxxxxxxxxxx>    
             midrange.com                                               cc 
                                                                           
                                                                   Subject 
             03/30/2006 09:27          Re: Embedded SQL Logical file use   
             AM                                                            
                                                                           
                                                                           
             Please respond to                                             
              RPG programming                                              
              on the AS400 /                                               
                  iSeries                                                  
             <rpg400-l@midrang                                             
                  e.com>                                                   
                                                                           
                                                                           




Yea, I failed to mention that I was posting for a colleague and I just
pasted his text into the post. He is using SQLRPGLE. This is static
SQL.

I have suggested that he try adding the select/omit logic from his
logical to the where clause, thinking that this might help give the
optimizer a better clue as to what he is trying to do.

He has written it in record level access and it runs in about the same
total time. The 25% time lost while the optimizer churns would be a
big boost if we could recover it.

Does the access plan not get stored with the *pgm object?

KC

On 3/30/06, Michael_Schutte@xxxxxxxxxxxx <Michael_Schutte@xxxxxxxxxxxx>
wrote:
> Kyle, You mentioned that you are using SQLRPG, can I assume that you
meant
> SQLRPGLE?  If not, try converting the code to SQLRPGLE.  I have a program
> that I prepare a statement and depending on the user selection, I pick
> which file that needs to be used in the SQL statement.
>
> For example, if the user chooses to search the employee master by last
> name, I chose a logical file that has the last name as the first part of
> the key.  If user chooses company and department, then I choose a logical
> file that has company and department first.  My processing time in this
> program went from 5 minutes to 10 seconds.
>
> Something in the WHERE statement is causing SQL to search for a better
> file.
>
> You could always not write it in SQL... Then you will not have to worry
> about it.
>
> Michael Schutte
> Work 614-492-7419
> email  michael_schutte@xxxxxxxxxxxx
>
>
>
>              "Kyle Collie"
>              <kcollie@xxxxxxxx
>              m>
To
>              Sent by:                  "RPG List" <rpg400-l@xxxxxxxxxxxx>
>              rpg400-l-bounces@
cc
>              midrange.com
>
Subject
>                                        Embedded SQL Logical file use
>              03/29/2006 06:41
>              PM
>
>
>              Please respond to
>               RPG programming
>               on the AS400 /
>                   iSeries
>              <rpg400-l@midrang
>                   e.com>
>
>
>
>
>
>
> We have an SQLRPG module that is opening a cursor based on a S/O
> logical. Everytime the program is run the query optimizer is checking
> for a better file to use and is eventually just coming back and using
> the initial S/O logical that we specified in the select statement. Is
> there a way to force the logical and prevent that processing time that
> is wasted when it tries to find a better index?
>
> The time it takes up is about 1 minute 20 seconds.
>
> Thanks in advance,
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.