Embedded SQL is fast (with the appropriate indexes, faster than RPG) if data
can be blocked and returned.
If only a single record must be read SQL is slower.
The first call is always slower than the subsequent executions of the same
SQL statement within the same activation group/module (depending on the
option set in the compile option CLOSQLCSR). The first time a full
optimization must occur, that means an access plan which consists of the
access paths to be used and the temporary objects to be built for executing
the SQL statement) must be either built by scratch or validated.
In RPG the file to be accessed is specified, so no optimization is
necessary.
After the access plan is created or validated the temporary objects must be
built and filled with data. (Data Path will be opened)
After the first execution the ODP will be deleted. The next time the access
plan will be checked again and the Data Path opened again.
After the second execution the ODP can be reused, that means only the data
will be updated. (ODP is reused)
In this way much more work is required for embedded SQL when accessing a
single row.
The compile option CLOSQLCSR *ENDMOD will delete the ODP each time the
module will be left, i.e. the ODP cannot be reused and a FULL OPTIMIZATION
must occur the next time the module is executed.
When executing dynamic SQL additional work must be done at runtime, i.e. the
String containing the SQL command must be checked (Syntax checking) and
converted into an executable SQL statement. Also the access plan for dynamic
SQL is not stored in the program object, contrary to the access plans used
by static SQL statements. If no access plan for the dynamic SQL Statement
is found in the SQE Plan Cache, the SQL Plan must be built by scratch.
As already pointed out SQL is fast for blocked reads.
In RPG you only can read the whole record. With (embedded) SQL you it is
possible to select and get returned only the data that are needed. In this
way less unneeded information is returned, and more necessary data can be
packed into a single block.
In this way avoid using SELECT * but only return what you want.
If you only want to check if at least record is available, you may use
something like this:
Select 1 into :isFound
From ...
Where ...
Fetch First Row Only;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Justin Taylor
Gesendet: Thursday, 05.4 2012 19:10
An: rpg400-l@xxxxxxxxxxxx
Betreff: Re: File Encapsulation Quandary
I did the OPEN/CHAIN/CLOSE as a worst case scenario. Without it, the CHAIN
consistently ran in less than .001. The SELECT INTO listed out all the
columns in the table and both the CHAIN and WHERE used the entire key.
I ran the tests several dozen times. In the same job, subsequent SELECT
INTO operations took a lot less than the initial 1/2 second, but never came
close to the the CHAIN, which maintained a consistent speed.
Ran on v5r4.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
