|
midrange.com
Rick,
Before I made the changes to use the SQL statement like I am now, I was
using a prepared statement... This new why is a lot faster... But I'm not
sure if it's actually faster or if access path is still present in memory.
I just tried where A1L03 is populated (we don't have any access paths over
that at all) and the results came back like it was nothing.
Anyway, I'm happy with the way it's working. You might like it too...
If you want to make sure at least 1 record exists, you could.
Select * from file where Exists( Select 1 from file where (your
conditions))
I don't know the exact syntax, but I think you'll get the ideal.
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:51 Re: SQLRPG - prepare, declare, no
PM cursor
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
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.
>
>
--
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.
