RE: Improve SQLRPGLE performance

[mwalter@xxxxxxxxxxxxxxx]

Sorry for the confusion. The post that I replied to didn't say anything
about an access path. If an access path was needed over the date portion,
I'd probably do that as well. But you couldn't CHAIN to it. You'd have to
SETLL, READ, and compare the date. That may be a bit cumbersome.

Thanks,

Mark

Mark D. Walter
Senior Programmer/Analyst
CCX, Inc.
mwalter@xxxxxxxxxx
http://www.ccxinc.com


                                                                                
                                                        
                      Joel Cochran                                              
                                                        
                      <jrc@xxxxxxxxxx>          To:       RPG programming on 
the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>                
                      Sent by:                  cc:                             
                                                        
                      rpg400-l-bounces@m        Subject:  RE: Improve SQLRPGLE 
performance                                              
                      idrange.com                                               
                                                        
                                                                                
                                                        
                                                                                
                                                        
                      08/04/2004 06:09                                          
                                                        
                      AM                                                        
                                                        
                      Please respond to                                         
                                                        
                      RPG programming on                                        
                                                        
                      the AS400 /                                               
                                                        
                      iSeries                                                   
                                                        
                                                                                
                                                        
                                                                                
                                                        




On Wed, 2004-08-04 at 13:44, mwalter@xxxxxxxxxxxxxxx wrote:
> CREATE VIEW myView AS SELECT DATE(myTimeStamp) as myDate, otherfields
FROM
> myPF
>
> Thanks,
>
> Mark
>
> Mark D. Walter
> Senior Programmer/Analyst
> CCX, Inc.
> mwalter@xxxxxxxxxx
> http://www.ccxinc.com

As we discussed (and I learned) recently, a View is not an index and
doens't produce one.  I think Peter was looking for a way to index the
date portion.  I tried creating an index with a columnar function like
so:

create index joellib/teststamp1 on joellib/teststamp ( date(stamp) )

The system wouldn't allow it, so I guess that isn't an option.  Maybe
the best answer is to create an index over the timestamp field and then
convert the date field to a timestamp in the program (or the SQL
statement):

create index joellib/teststamp1 on joellib/teststamp ( stamp )

<psuedo-code>
 /free
    myStamp = %timestamp( myDate );
 /end-free
c/exec sql
c+ select someFields from myFile
c+   where <<someCondition>>
c+   order by myStamp
c/end-exec
</psuedo-code>

Joel
http://www.rpgnext.com


--
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.