Re: SQL and performance

[Dieter.Bender@xxxxxxxxxxx (Dieter Bender)]

On Wednesday 26 February 2003 11:07, you wrote:
> So the advantage is you can test your SQL without using causing CFINIT to
> cripple the machine - a big bonus.

but you'll get no knowledge about the decisions of the query optimizer for 
your production environment. the sql statement might be performed diffrent 
with CF$NT and without CF$NT

Dieter

>
> -----Original Message-----
> From: Dieter.Bender@xxxxxxxxxxx [mailto:Dieter.Bender@xxxxxxxxxxx]
> Sent: 26 February 2003 09:54
> To: Java Programming on and around the iSeries / AS400
> Subject: Re: SQL and performance
>
>
> Hi,
>
> On Wednesday 26 February 2003 10:34, you wrote:
> > Biggest SQL performance tip - always test your SQL statement by running
> > it in debug mode, either by doing a STRDBG,
>
> this will work
>
> or by running it from Ops Nav.
>
> this won't work in this case, Oops Nerv will run it in a server job,
> without
>
> CF$NT
>
>
> Dieter
>
> > This will generate a load of messages in your joblog explaining what SQL
> > has done to execute your query. If it has had to create an access path,
> > it will tell you what it has created, and why it has rejected the
> > existing access paths.
> >
> > You can then improve the performance by creating the recommended access
> > path, or try and work out why your query can't use an existing access
>
> path.
>
> > Chris.
> >
> > -----Original Message-----
> > From: P.Goovaerts@xxxxxxxxxx [mailto:P.Goovaerts@xxxxxxxxxx]
> > Sent: 26 February 2003 09:17
> > To: web400@xxxxxxxxxxxx
> > Cc: java400-l@xxxxxxxxxxxx
> > Subject: SQL and performance
> >
> >
> > First, excuse if this mail is in the wrong mailing-list but there's no
> > SQL specific...
> >
> > We have had some problems because of 'lack of CPW' (CFINT01-job taking
> > 75% of our system).  Checking out what jobs caused this pointed us to RPG
> > programs using SQL.
> > Giving the fact that we have files with about 1 million records and we
> > want to enhance our RPG with SQL:
> >
> > 1) i.e. a file with 250000 records which is accessed by 'select * from
> > where'
> > What exactly happens at system level?
> > - are all records read when SQL is executed?
> > I alway's thought that
> > - a 'performant' index (lf) was searched
> > - if necessary, a new index is created automaticallyindien nodig deze
> > index eventueel gecreëerd wordt
> > - ALL records are read
> >
> > One of our test shows that only about 20000 records are read...
> >
> > 2) join-union
> > Any tips for performance welcome!
> >
> > 3) What is the impact when creating tables inside our RPG/SQL program?
> > example:
> > - create tableX
> > - select records using table1 / table2/,... /tableN
> > - put result in tableX
> > - select records from tableX
> >
> > I alway's thought that I/O is the prime attacking job on the system but,
> > if IBM say's it's going to change RPG to use SQL in stead of native I/O
> > (read, reade, readp), it seems that this will have huge impact on CPW
> > too...
> >
> > Finally, I know... getting more CPW solves this issue too but we first
> > want to be sure that our developing is ok!
> > _______________________________________________
> > This is the Java Programming on and around the iSeries / AS400
> > (JAVA400-L) mailing list
> > To post a message email: JAVA400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/java400-l
> > or email: JAVA400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/java400-l.
> >
> > _______________________________________________
> > This is the Java Programming on and around the iSeries / AS400
> > (JAVA400-L) mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/java400-l
> > or email: JAVA400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/java400-l.

-- 
mfG

Dieter Bender


DV-Beratung Dieter Bender
Wetzlarerstr. 25
35435 Wettenberg
Tel. +49 641 9805855
Fax +49 641 9805856
www.bender-dv.de