RE: SQL and performance

["Price, Chris" <chris_price@xxxxxxxxx>]

Biggest SQL performance tip - always test your SQL statement by running it
in debug mode, either by doing a STRDBG, or by running it from Ops Nav. 

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.