RE: SQL query optimization -- RPG400-L

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