|
Mike, Try running it with half of the host variables populated and half blank, then change the where clause by putting the compares of the host variable to blanks before the compares to the file fields, run it again and see if that makes it faster. On 4/12/06, Michael_Schutte@xxxxxxxxxxxx <Michael_Schutte@xxxxxxxxxxxx> wrote: > Not sure. not the big SQL guru. Just trail and error on my part. :)) . > Anyway, I'm testing it right now, with 550,000 records, I'm getting > probably just under a 2 second turnaround. > > Michael Schutte > Work 614-492-7419 > email michael_schutte@xxxxxxxxxxxx > > > > "rick baird" > <rick.baird@gmail > .com> To > Sent by: "RPG programming on the AS400 / > rpg400-l-bounces@ iSeries" <rpg400-l@xxxxxxxxxxxx> > midrange.com cc > > Subject > 04/12/2006 02:09 Re: SQLRPG - prepare, declare, no > PM cursor > > > Please respond to > RPG programming > on the AS400 / > iSeries > <rpg400-l@midrang > e.com> > > > > > > > Thanks Mike, > > I don't have a big problem with this at all, except that it might be > slower? > > I wonder, would it have to evaluate the host variable (A1L0x) for each > record retrieved to determine it's inclusion? > > On 4/12/06, Michael_Schutte@xxxxxxxxxxxx <Michael_Schutte@xxxxxxxxxxxx> > wrote: > > Rick, > > > > I use this type of code all the time. It works really well. > > > > WHERE PRER = :A1ER > > AND (PRL01 = :A1L01 Or :A1L01 = ' ') > > AND (PRL02 = :A1L02 Or :A1L02 = ' ') > > AND (PRL03 = :A1L03 Or :A1L03 = ' ') > > AND (PRL04 = :A1L04 Or :A1L04 = ' ') > > AND (SUBSTRING(PREN,6,4) = :A1EN4 Or :A1EN4 = ' ') > > AND (PRFNM LIKE (CASE > > WHEN :FName <> ' ' THEN :FName > > ELSE ' ' > > END) > > OR :FName = ' ') > > > > It will only pick records, when my host variables are populated. > > > > Michael Schutte > > Work 614-492-7419 > > email michael_schutte@xxxxxxxxxxxx > > > > > > > > "rick baird" > > <rick.baird@gmail > > .com> To > > Sent by: "RPG programming on the AS400 / > > rpg400-l-bounces@ iSeries" <rpg400-l@xxxxxxxxxxxx> > > midrange.com cc > > > > Subject > > 04/12/2006 12:55 Re: SQLRPG - prepare, declare, no > > PM cursor > > > > > > Please respond to > > RPG programming > > on the AS400 / > > iSeries > > <rpg400-l@midrang > > e.com> > > > > > > > > > > > > > > sort of. > > > > the problem is dynamically assembling my where clause. don't know > > until runtime what I'm going to select on. > > > > For what it's worth - I ended up changing it anyway - getting the > > count is going to be much slower than fetching one record (optimize > > for 1 record) - the requirement is to check for a valid combination of > > fields in this file, and it's only checking, not listing or updating. > > > > so i'm going to fetch one record to validate instead of returning a > > count - I don't care how many, only that one exists, and I want it to > > run as quickly as possible. > > > > thanks to all who helped. > > > > On 4/12/06, Thomas Gard <thomas.gard@xxxxxxxxx> wrote: > > > Is this what you want? > > > > > > C/EXEC SQL -- SQL STATMENTS > > > C+ SELECT COUNT(*) > > > C+ INTO :COUNTER > > > C+ FROM lmsfillib/jobque > > > C/END-EXEC > > > > > > -- > > > 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. > > > > > > > > > > -- > > 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. > > > > > > > > -- > > 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. > > > > > > -- > 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. > > > > -- > 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
