|
excuse my ignorance. Whats RLA? -----Original Message----- From: Tom Daly [mailto:Tdaly@sddsystems.com] Sent: Monday, February 11, 2002 4:23 PM To: 'rpg400-l@midrange.com' Subject: RE: SQL query optimization Just my 2 cents.... this is the kind of stuff that makes me love RLA! | -----Original Message----- | From: Smith, Mike [mailto:Mike_Smith@RGCResources.com] | Sent: Monday, February 11, 2002 16:16 | To: 'rpg400-l@midrange.com' | Subject: RE: SQL query optimization | | | THANKS alexei | here is what showed up as a result | 4 - The cost to use this access path, as determined by | the optimizer, was | | higher than the cost associated with the chosen access method. | | | my file has only 1 keyfield. that is the orderby in my select. | | How can that have a higher cost, than it doing a sort. | Makes no sense to | me. | | -----Original Message----- | From: Alexei Pytel [mailto:pytel@us.ibm.com] | Sent: Monday, February 11, 2002 2:55 PM | To: rpg400-l@midrange.com | Subject: RE: SQL query optimization | | | | This message should have explanation (in the second level | text) of why | optimizer chose not to use access path in favour of sort: | All access paths were considered for file QACAT00. | | Alexei Pytel | | | | | "Smith, Mike" | <Mike_Smith@RGCRes To: | "'rpg400-l@midrange.com'" <rpg400-l@midrange.com> | ources.com> cc: | Sent by: Subject: | RE: SQL query | optimization | rpg400-l-admin@mid | range.com | | | 02/11/2002 12:31 | PM | Please respond to | rpg400-l | | | | | | Ok Darren, | | here is the actual select statement that is being declared. | | 'SELECT QCCAT, QCCACT FROM QACAT00 ORDER BY QCCAT ' | | here is part of the joblog when i run it through debug | Embedded SELECT completed. | PREPARE of statement #SEL completed. | Unable to retrieve query options file. | All access paths were considered for file QACAT00. | Arrival sequence access was used for file QACAT00. | Temporary result file built for query. | ODP created. | Blocking used for query. | Cursor CTLCURS opened. | | | The keyfield in the file is 'QCCAT' | | here is the code that builds and opens the cursor. | | ***************************************************************** | C $CURSOR BEGSR | C IF #POSTO > *BLANKS | C EVAL #SELCT = %REPLACE(#WHERE: | C SQLARY(1): | C | %SCAN('#':SQLARY(1)): | C 1) | C ELSE | C EVAL #SELCT = %REPLACE(' ': | C SQLARY(1): | C | %SCAN('#':SQLARY(1)): | C 1) | C ENDIF | C EVAL #SELCT2 = %REPLACE(#SORTFLD: | C #SELCT: | C %SCAN('@':#SELCT)) | C/EXEC SQL | C+ PREPARE #SEL FROM :#SELCT2 | C/END-EXEC | C/EXEC SQL | + Declare CTLCURS SCROLL Cursor For #SEL | C/END-EXEC | | *OPEN THE CURSOR | C/EXEC SQL | + Open CTLCURS | C/END-EXEC | * if already opened close the cursor then reopen | C IF SQLCOD = -00000502 | C EXSR $CLOSECURS | *OPEN THE CURSOR | C/EXEC SQL | + Open CTLCURS | C/END-EXEC | | | -----Original Message----- | From: darren@dekko.com [mailto:darren@dekko.com] | Sent: Monday, February 11, 2002 1:21 PM | To: rpg400-l@midrange.com | Subject: Re: SQL query optimization | | | | >Why is it not using the access path of my logical? (Mike) | | >> Could be many reasons. (Gary) | | Quite right Gary. Mike, if you could paste in the actual | statement and | identify the key fields in the file perhaps we could narrow down the | reasons for you. | | | _______________________________________________ | This is the RPG programming on the AS400 / iSeries | (RPG400-L) mailing list | To post a message email: RPG400-L@midrange.com | To subscribe, unsubscribe, or change list options, | visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l | or email: RPG400-L-request@midrange.com | 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@midrange.com | To subscribe, unsubscribe, or change list options, | visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l | or email: RPG400-L-request@midrange.com | 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@midrange.com | To subscribe, unsubscribe, or change list options, | visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l | or email: RPG400-L-request@midrange.com | 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@midrange.com | To subscribe, unsubscribe, or change list options, | visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l | or email: RPG400-L-request@midrange.com | 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@midrange.com To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l or email: RPG400-L-request@midrange.com 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
