Re: SQL and performance

[Dieter.Bender@xxxxxxxxxxx (Dieter Bender)]

On Wednesday 26 February 2003 10:17, you wrote:
> First, excuse if this mail is in the wrong mailing-list but there's no SQL
> specific...

you are posting to the wrong list, I'm posting the wrong statements to the 
right list, or my spelling of something is wrong - where is the diffrence?

>
> 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?

It depends 
        - are you using static or dynamic sql
        - is the statement already prepared
        - is there an access path, the qury optimizer uses
        - how many records of all match the query

> - 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 automatically

those indexes are temporary and this is rather expensive
If you take the job under debug, the query optimizer sends some diagnostic 
statements to the joblog, what he's doing.

indien 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!

join: don't use casting or other derived fields for join operations; there 
has to be a suitable index over the join fields on all tables.

union: avoid union if possible, it's very expensive on as400

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

It would be better to put the records to an already existing table with all 
needed access pathes.

>
> 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!

another solution might be to seperate the database and the application, then 
the database related workload has no CF$NT

> _______________________________________________
> 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