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



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

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.