|
midrange.com
FYI, final results:
I tried both ways ('1' and IBMREQD) for my result set with no
degredation in performance either way, so I left it as '1' so that I
could load an indicator field for asthetic reasons. ( if not
$itemfound)
Using EXISTS cut the response time from about 20-30 seconds to 6-10
seconds on a LIKE compare similar to this: '_____ABC_DEFG' where
the first record that matched was near the end of the access path -
1011AAABC1DEFG'
not great, but a lot better and acceptable.
thanks again,
Rick
On 4/18/06, rick baird <rick.baird@xxxxxxxxx> wrote:
> Charles and Michael,
>
> I see now that you are correct. (never should have doubted) my way
> returned more than one record, and bombed (which could have been
> corrected by a fetch 1, but who wants to do that?)
>
> It's interesting that they'd put a dummy file with a dummy record out
> in sysibm for that very reason. Handy, but looks like a kludge to
> overcome a weakness.
>
> Thanks to both of you,
>
> Rick
>
> On 4/18/06, Michael_Schutte@xxxxxxxxxxxx <Michael_Schutte@xxxxxxxxxxxx> wrote:
> > Rick I think between all the available answers I believe that Charles is
> > right.
> >
> > Do this logic, pulled from another user.
> >
> > myInd = 'N';
> >
> > Select IBMREQD into :myInd from sysibm/sysdummy1 where exists (Select 1
> > from myfile where thekey like '%abc%')
> >
> > If found in the subselect... myInd will have a 'Y', it's the only
> > value/record in sysdummy1.
> >
> >
> >
> > Michael Schutte
> > Work 614-492-7419
> > email michael_schutte@xxxxxxxxxxxx
> >
> >
> >
> > "Wilt, Charles"
> > <CWilt@xxxxxxxxxx
> > om> To
> > Sent by: "RPG programming on the AS400 /
> > rpg400-l-bounces@ iSeries" <rpg400-l@xxxxxxxxxxxx>
> > midrange.com cc
> >
> > Subject
> > 04/18/2006 01:02 RE: SQL to only check for
> > PM existence:
> >
> >
> > Please respond to
> > RPG programming
> > on the AS400 /
> > iSeries
> > <rpg400-l@midrang
> > e.com>
> >
> >
> >
> >
> >
> >
> > Using EXISTS is the best why to do what you're asking for.
> >
> > EXISTS is specifically designed to answer the question you are asking
> > and by using it the query engine knows exactly what you are looking for.
> >
> > You may get performance as good sometimes with Michael's method, but you
> > will never get better and you could get worse.
> >
> > EXISTS is also part of the SQL standard.
> >
> > I do wish IBM would allow something like so:
> > c/exec SQL
> > c+ VALUES ( exists (select '1' from MYFILE where THEKEY =
> > 'SOMEKEY'))
> > c+ into :myFlag :nullInd
> > c/end-exec
> >
> > c if %nullInd
> > // record does not exist
> > c else
> > // record exists
> > c endif
> >
> >
> > HTH,
> >
> > Charles Wilt
> > --
> > iSeries Systems Administrator / Developer
> > Mitsubishi Electric Automotive America
> > ph: 513-573-4343
> > fax: 513-398-1121
> >
> >
> > > -----Original Message-----
> > > From: rpg400-l-bounces@xxxxxxxxxxxx
> > > [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rick baird
> > > Sent: Tuesday, April 18, 2006 11:25 AM
> > > To: RPG programming on the AS400 / iSeries
> > > Subject: Re: SQL to only check for existence:
> > >
> > > Thanks Charles - very similar to Michael's answer, but with
> > > another layer.
> > >
> > > If I get performance problems where the first match is way down in the
> > > access path, I'll try this too.
> > >
> > > at this point, the select without the exists() looks like a winner.
> > >
> > > On 4/18/06, Wilt, Charles <CWilt@xxxxxxxxxxxx> wrote:
> > > > Check out the EXISTS predicate.
> > > >
> > > > Select 1
> > > > >From sysibm/sysdummy1
> > > > Where exists (select 1 from my where myfield like '___abc___')
> > > >
> > > > HTH,
> > > >
> > > >
> > > > Charles Wilt
> > > > --
> > > > iSeries Systems Administrator / Developer
> > > > Mitsubishi Electric Automotive America
> > > > ph: 513-573-4343
> > > > fax: 513-398-1121
> > > >
> > > >
> > > > > -----Original Message-----
> > > > > From: rpg400-l-bounces@xxxxxxxxxxxx
> > > > > [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rick baird
> > > > > Sent: Tuesday, April 18, 2006 10:56 AM
> > > > > To: Midrange Systems Technical Discussion; RPG programming on
> > > > > the AS400 / iSeries
> > > > > Subject: SQL to only check for existence:
> > > > >
> > > > > hey all,
> > > > >
> > > > > Is there a way to run an SQL statement that doesn't (necessarily)
> > > > > return anything, but only checks for the existence of one or more
> > > > > records based on a where statement - similar to a SETLL
> > > and %found?
> > > > >
> > > > > I've got a rather complicated SQL statement using:
> > > > > LIKE '___abc___' or it could be:
> > > > > LIKE ______xyz'
> > > > >
> > > > > - over a very large file, and I'm having performance issues.
> > > > >
> > > > > I don't want to do a count(), because I don't care how
> > > many, only that
> > > > > at least one exists. with count, it would have to read
> > > through the
> > > > > entire file to determine it.
> > > > >
> > > > > I also tried just doing a select - optimize for 1 row and a single
> > > > > fetch, but that seemed to take forever too. It appears
> > > as if it is
> > > > > searching the entire file, instead of stopping at the first one.
> > > > >
> > > > > help!
> > > > >
> > > > > Rick
> > > > >
> > > > > --
> > > > > 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.
