|
midrange.com
One *possible* explanation:
depending on file characteristics - size, records length, number of fields
- it could be faster to read entire file (fast sequential I/O), pick up
just your 2 fields and then sort them - then to go through access path and
bring in record by record (slow random I/O).
Another *possible* explanation - for a very small file, it's faster to read
file in arrival sequence then to bother to use index etc.
E.g. if you debug your program with a small test file, you may see
optimizer to make decisions very different from what you will see with a
production-size file.
In any case, if you want to bend optimizer to use access path, disallow
file copy - e.g. ALWCPYDTA(*NO) on STRSQL or equivalent parameter on SQL
preprocessor commands.
Alexei Pytel,
speaking only for myself
"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 03:15
PM
Please respond to
rpg400-l
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.
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.
