RE: compare using qry or logical

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Build a logical that is always there - not on the fly. Don't have any S/O 
specifications, just the logical. Use the STRDBG and optimizer messages to 
see what is going on. Then use the same OPNQRYF statement - it'll probably 
use the new logical to find that nothing is there.

You should also look at the OPTIMIZE parameter, which can help the 
optimizer make better choices. A value of *FIRSTIO and a small number of 
records will bias things toward using an index. A large number of records 
biases it toward table scan. I think *MINWAIT tends toward index use, as 
well, don't remember for sure.

So for the one value you could use 1 for # of records, 100,000 for the 
other, say.

HTH
Vern

At 11:02 AM 10/6/2004, you wrote:
> In that case, I recommend trying the logical.  That should definitely save
> you the 30 minutes.
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "Dwayne Allison" <Dwayne.Allison@xxxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 10/06/2004 10:56 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
>
> To
> "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
> cc
> Andy Tran <Andy.Tran@xxxxxxxxx>
> Fax to
>
> Subject
> RE: compare using qry or logical
>
>
>
>
>
>
> What we have is a program that run Monday through Thursday nigh within our
> cycle.  This program is called twice.  The first with the OPNQRYF
> FILE((PASPBR)) QRYSLT('ARBGRP *EQ "*R*"') and then with the OPNQRYF
> FILE((PASPBR)) QRYSLT('ARBGRP *EQ "*C*"'.  We have records that equal *R*,
> but none in the file that equal *C*.  I can go along with it taking a
> while to process the records with an *R*.  But if we have no *C*, why is
> it taking thirty minutes to process.  It should read the file and find no
> record are there and end.
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of rob@xxxxxxxxx
> Sent: Wednesday, October 06, 2004 10:47 AM
> To: Midrange Systems Technical Discussion
> Subject: Re: compare using qry or logical
>
>
> There are various speed implications here.
> 1)  The speed of the actual generation of the OPNQRYF versus the actual
> generation of the logical file, or view.
> 2)  The speed of access using the file via OPNQRYF versus the speed of
> access using the logical file.
> 3)  The sum total of 1 & 2 done via OPNQRYF versus using a logical file.
> 4)  Any performance implications of leaving a logical file out there might
>
> have on database updates.  (I have a 1998 copy of the AS/400 Experts
> journal Volume I, Number I on my desk.  It has an article in there by Skip
>
> Marchesani in which he states that it is a MYTH to believe that "logical
> files on a file are resource intensive, have a negative impact on
> performance, and should only be used when absolutely necessary".  He goes
> on to state that IBM cleaned this up on V2 of OS/400 and even better in V3
>
> of OS/400.  Others say they have testing that disputes Skip's statements.)
>
> If 4 doesn't prove to be a concern in your environment then item 1 drops
> out of the picture if you leave the logical file out there.  This will, in
>
> turn, shorten item 3.  But if you're deleting/rebuilding the logical file
> all the time you're probably losing any performance improvement.
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "Dwayne Allison" <Dwayne.Allison@xxxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 10/06/2004 10:30 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
>
> To
> "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
> cc
>
> Fax to
>
> Subject
> compare using qry or logical
>
>
>
>
>
>
> We are using the following command:
>
>
> OVRDBF     FILE(PA0PBR) TOFILE(PASPBR) SHARE(*YES)
> OPNQRYF    FILE((PASPBR)) QRYSLT('ARBGRP *EQ "*C*"')
>
>
>
> Is it quicker to create a logical file with a select ARBGRP that equal *C
> only or use the above query?
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.