RE: sqlrpgle select statements -- RPG400-L

Hi Matt - I'm still not too sure if my question has been answered. Staying
with my example, i.e.
the physical file FILEA has 10,000,000 records, but the logical FILEB of
that physical has only 50,000 records due to the DDS SELECT/OMIT,
If I create an SQL against the logical (which has omitted 9,500,000 due to
lets say a birthdate not = 04/01/2001) will the results INCLUDE any of the
omitted records that the logical should NOT have?

For example
select * from FILEB ORDER BY LAST_NAME

Will this result in a list of 50,000, or a list of 10,000,000?

Thanks in advance



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx


                                                                       
             <Matt.Haas@thomso                                         
             n.com>                                                    
             Sent by:                                                   To
             rpg400-l-bounces@         <rpg400-l@xxxxxxxxxxxx>         
             midrange.com                                               cc
                                                                       
                                                                   Subject
             03/02/2006 11:33          RE: sqlrpgle select statements  
             AM                                                        
                                                                       
                                                                       
             Please respond to                                         
              RPG programming                                          
              on the AS400 /                                           
                  iSeries                                              
             <rpg400-l@midrang                                         
                  e.com>                                               
                                                                       
                                                                       




What it will likely do is build a temporary access path over FILEA and
use that. As you can imagine, that doesn't happen quickly with a 10
million row file. For smaller files or if there is a logical without
select/omit that partially satisfies the where and order by clauses, it
may do table scans using those logicals to build a temporary table and
then sort the temporary table which may be somewhat faster than building
a temporary access path but is still pretty slow or just do a full table
scan (as in read every record).

Generally speaking, you want the key fields in your logical to match the
fields in your where clause followed by the fields in your order by
clause (you want these fields to exactly match or the system may still
build a temporary table and sort it to return the records in the order
you want).

For example, you have this query:

SELECT CUSTNUM, CUSTNAME FROM ORDERS WHERE COUNTRY='US' AND STATUS='BO'
ORDER BY STATE, ZIPCODE, CITY

You'd want to specify COUNTRY and STATUS as the first two key fields but
which one comes first doesn't matter that much (it is better to specify
them in the same order as in the query but the optimizer will figure
that out if you don't). STATE, ZIPCODE, and CITY, however, must be
specified in that order to keep the query optimizer from either ignoring
that logical or using it but creating a temp table and sorting it.

Example 2:

SELECT CUSTNUM, CUSTNAME FROM ORDERS WHERE COUNTRY='US' AND STATUS='BO'
ORDER BY ZIPCODE, CITY

If you run this query, the optimizer may choose not to use the logical
you created for the first query, or it may build a temp table and sort
it because the key fields in the logical don't exactly satisfy the where
and order by clauses, or it may build an access path based on the
logical created above (which one it does depends upon a number of things
including number of records in the file). In this case, we know that zip
codes don't cross states so if it doesn't use the logical you want it
to, changing the query to this:

SELECT CUSTNUM, CUSTNAME FROM ORDERS WHERE COUNTRY='US' AND STATUS='BO'
AND STATE=STATE ORDER BY ZIPCODE, CITY

Will very likely result in it using the proper logical since the logical
now exactly satisfies the where and order by clauses.

This may seem like a bunch of voodoo but the query optimizer doesn't
have any knowledge of what you're trying to accomplish so the more hints
that you give it, the better job it will do at selecting the proper
logical.

One other thing, if you add CUSTNUM and CUSTNAME as key fields after the
existing one, SQL will get those values from the index instead of having
to actually read the file. With large files, this can dramatically speed
up the query. It's not always appropriate to do this since it can make
the access paths quite large but it is another tool you can use to
improve performance (especially if you combine this with multi-row
fetches and create SQL indexes instead of logical files).

Matt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Thursday, March 02, 2006 10:35 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: sqlrpgle select statements


Are you saying that if the physical file FILEA has 10,000,000 records,
but
the logical FILEB of that physical has only 50,000 records due to the
DDS
SELECT/OMIT, an SQL against the logical will STILL process 10,000,000
and
NOT 50,000?



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx




             <Matt.Haas@thomso

             n.com>

             Sent by:
To
             rpg400-l-bounces@         <rpg400-l@xxxxxxxxxxxx>

             midrange.com
cc



Subject
             03/02/2006 10:13          RE: sqlrpgle select statements

             AM





             Please respond to

              RPG programming

              on the AS400 /

                  iSeries

             <rpg400-l@midrang

                  e.com>









If you have select/omit criteria in the logical file, the query
optimizer will not consider it when determining the best access path.
When you include select/omit criteria in DDS based logical files, they
are both an index and a view at the same time which isn't normal in the
SQL world. The SQL book that explains the query optimizer is a very good
read if you are getting into SQL programming. Most things make sense
once you understand how it works but yes, there are times when you will
need to tweak the query a little bit (like adding flda=flda in the where
clause to get it to use the index with flda that it otherwise would
skip) to get it to use a better index but those types of tweaks are not
unique to the built in database.

Matt
<snip>

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