|
50,000.... I just tested it... -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore Sent: Thursday, March 02, 2006 12:06 PM To: RPG programming on the AS400 / iSeries Subject: RE: sqlrpgle select statements 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.
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
