× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi guys,

just a few remarks about static compared with dynamic SQL.
1. With static SQL the access plan(s) get stored in the (service-)program
objects
   and can be validated and reused each time the SQL statement gets
performed.

2. The first time a (static) SQL statement gets executed the query optimizer
   validates and updates the existing access paths and searches trough the
   existing access paths to find the optimal one. The SQL statement gets
executed
   using this access paths. After execution the ODP (open data path) gets
closed.
   The second time the same statement gets executed the query optimizer
checks
   again the access plans and compares it with the access path determined
   at the first execution time. If the ODP is reuseable it stays open after
   the second execution.
   Beginning with the thired execution the already opened ODP gets reused,
   without the overhead of revalidating and determining the optimal access
path.
   This only works if the activation group is not *NEW and the option
CLOSQLCSR
   is *ENDACTGRP. If the option CLOSQLCSR is set to *ENDMOD, the ODP never
stays open
   and never can be reused. For performance issues *ENDACTGRP should be
used.
   If different WHERE-Clauses can be used for the same SQL statement,
   several access paths are stored in the (service-)program objects and can
be validated.
   If the WHERE-Clause changes the query optimizer may revalidate and
   reoptimize the access path.

   Note: Each SQL statement gets it's own ODP. That means if the same
statement
         is copied and executed several times, each of these copies gets
validated
         and gets its own ODP.

         The best would be to collect SQL statements in procedures in
Service programs
         and create the service program with a named activation group.
         In this way the number of ODPs can be minimized.

   Access plan stored in the service program objects can be displayed by
using the
   CL command PRTSQLINF.

3. With dynamic SQL there is even more overhead, because syntax checking
   must be executed at run time.
   Each time a PREPARE or EXECUTE IMMEDIATE statement gets executed,
   the access plans must be built from scratch and the optimal access path
must be determined.
   In contrary to static SQL the access plans get NOT stored in the
   (service) program objects, that means validation against these access
plans
   is not possible.

   Note: with the new SQE (SQL query engine) all access plans from all SQL
statements
         (executed by the SQE) get stored in the systemwide plan cache and
can be
         used for validating and determining the optimal access path.
         (even with dynamic SQL!)


Mit freundlichen Gruessen / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von
Michael_Schutte@xxxxxxxxxxxx
Gesendet: Mittwoch, 12. April 2006 21:02
An: RPG programming on the AS400 / iSeries
Betreff: Re: SQLRPG - prepare, declare, no cursor


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.



--
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 thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.