Re: SQL and performance

[Dieter.Bender@xxxxxxxxxxx (Dieter Bender)]

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