Hi,
The CQE cannot handle the following:
V5R3: INTERSECT, EXCEPT
V5R4: OLAP-Ranking Functions such as ROW_NUMBER, RANK, DENSE_RANK
Recursive Common Table Expressions
V6R1: Enhancements in the Groupie By clause ROLLUP, CUBE and Grouping Sets
The list above is not complete. There will be other enhancements that cannot
be used by the CQE either.
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 David FOXWELL
Gesendet: Friday, 10. October 2008 09:23
An: RPG programming on the AS400 / iSeries
Betreff: RE: SQL versus READ for sequential processing
Hi Birgitta,
Have you an example of enhancements of SQE that cannot be used in this case?
I don't think you mean that certain SQL commands wouldn't execute, oder?
Thanks for the information. I will inform the rest of the shop and search
all our SQLRPG's for such misuse while the list is still comparatively
small.
-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] De
la part de Birgitta Hauser
Envoyé : jeudi 9 octobre 2008 19:43
À : 'RPG programming on the AS400 / iSeries'
Objet : AW: SQL versus READ for sequential processing
Hi David,
My first embedded SQL statement used a DDS LF.
I realized sometime after that I only needed to use the PF.
Is there a problem in leaving it like that? As the rest of our shop is
very new to SQL, I think others may still be doing the same.
First SQL works differently than native I/O does.
With native I/O you specify a physical or logical file in the F-Specs which
is read.
If you specify DDS described logical file in an SQL statement, the query
optimizer first have to rewrite the SQL statement based on the physical
files.
In the first step the query optimizer does not care about the keys specified
in the logical file, but takes only the selected fields, join information
and select/omit clauses to rewrite the SQL statement. After having the SQL
statement rewritten the optimizer does not know anymore that originally a
keyed logical file was specified and searches through ALL available access
paths (DDS described logical files and SQL indexes) to find the best one.
Since release V5R2 there are 2 query engines, the Classic Query Engine
(CQE), the old one and the SQL Query Engine (SQE), the new one.
In most cases the SQE performs much better than the CQE. Parts of the SQE
are implemented under the MI level (directly in the operating system). Also
the SQE is designed in OO style and therefore much more flexible than the
CQE. Further all SQL enhancements since release V5R3 can only be executed by
the SQE.
When specifying a DDS described logical file, the SQL statement must be
rerouted to the CQE, which may not only cost between 10 and 15% of
performance, but the flexibility and the enhancements of the SQE cannot be
used.
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 David FOXWELL
Gesendet: Thursday, 09. October 2008 08:55
An: RPG programming on the AS400 / iSeries
Betreff: RE: SQL versus READ for sequential processing
Hi Birgitta,
My first embedded SQL statement used a DDS LF. I realised sometime after
that I only needed to use the PF. Is there a problem in leaving it like
that? As the rest of our shop is very new to SQL, I think others may still
be doing the same.
-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] De
la part de Birgitta Hauser Envoyé : jeudi 9 octobre 2008 07:09 À : 'RPG
programming on the AS400 / iSeries'
Objet : AW: SQL versus READ for sequential processing
Hi,
I normally advise that embedded SQL should only be used where there is
an
apparent advantage to using native I/O,
namely in fetching complex result sets that are more difficult to
accommodate via native I/O access.
I have had to rewrite code where SQL zealots have essentially replaced
a
bunch of reads, which should have been compiled into the program as native
I/O using a few lines >>of RPG, with verbose SQL code that ends up running 8
times slower. This would not be surprising since each SQL statement is
pre-compiled as a dynamic call to the SQL CLI.
I don't agree. Using embedded SQL allows you to reduce your source code. A
bunch of reads and chains combined with a lot of nesting (or a lot of iters
or leaves), is much harder to maintain. Creating the appropriate views and
read from them with embedded SQL is much easier to maintain. If there is a
problem you have a look into your program and can see the view. After you
execute the SQL statement interactively based on this view. If the record
you are looking for is not in the view, you may check the view (joins, where
conditions) and check the rows. It is even possible to create Query/400 and
allow the users to check the data by themselves.
But agreed, if you are using embedded SQL you should know how SQL works and
how to tune it, what are the pitfalls, what you never should do (for example
specifying a DDS described logical file in a SQL statement or accessing the
file with the relative record no), which indexes/access paths are needed....
There is a big difference between a table scan over a 80,000,000 row file or
an index access over the same file.
I only use native I/O for Chains or SetLL because both are much faster than
the SQL equivalents, but for the rest I only use embedded SQL.
Coding is now much faster than native I/O, but there is more work to tune
the SQL statements.
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 Peter Connell
Gesendet: Wednesday, 08. October 2008 23:51
An: RPG programming on the AS400 / iSeries
Betreff: RE: SQL versus READ for sequential processing
I normally advise that embedded SQL should only be used where there is an
apparent advantage to using native I/O, namely in fetching complex result
sets that are more difficult to accommodate via native I/O access.
I have had to rewrite code where SQL zealots have essentially replaced a
bunch of reads, which should have been compiled into the program as native
I/O using a few lines of RPG, with verbose SQL code that ends up running 8
times slower. This would not be surprising since each SQL statement is
pre-compiled as a dynamic call to the SQL CLI.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Charles Wilt
Sent: Thursday, 9 October 2008 7:54 a.m.
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL versus READ for sequential processing
RPG is and probably always will be faster than SQL for Record Level Access
(RLA). RPG is optimized for RLA whereas SQL is optimized for processing
sets of records. At some point SQL might match RPG's speed with a single
row fetch, but I'd be willing to bet it will never be faster.
Here's the motto I tell developers new to SQL, if you're using a cursor
you're probably doing something wrong.
If you want an SQL cursor to beat RPG, you have to do a multi-row fetch of
100+ records or so at a time.
I'd be curious what IBM'er told you SQL FETCH was faster than RPG.
Some other benchmarks you may find interesting:
http://archive.midrange.com/rpg400-l/200809/msg00263.html
Charles Wilt
On Wed, Oct 8, 2008 at 9:57 AM, Åke Olsson <ake.olsson@xxxxxx> wrote:
On a speech from an IBM'er at the conference I attended two days back
he
claimed that SQL FETCH was a much faster method for accessing data than the
RPG READ.
Dramatic performance improvements were promised. I decided to do a
test as
follows:
I found a file with 62K records and created another file with same
fields
by "create table". Copied (mapped) the data to the new file.
I created two programs with same process (read all records do some
minor
processing on one of the fields, log on a printer file time program starts
and ends).
Program A used regular read operations on the original file and
program B
declared a cursor on the new sql-created copy.
The results I got surprised me. The SQL version was significantly slower.
"READ" comleted at .09 seconds and "FETCH" needed .541 seconds. Thus is 6
times slower!
There was no index involved, just plain sequential processing here.
Could I have missed out on any prerequisites for speeding up toe process.
This is the code for my test programs
1/ "READ" program
Fqsysprt o f 120 printer
Falcorl if e disk
D tstart s t
D tend s t
d stora s like(rlstor) dim(999)
D ix s 5 0
D tcount s 7 0
/free
tstart=%timestamp;
read alcorl;
dow not %eof;
tcount=tcount+1;
if %lookup(rlstor:stora)=*zero;
ix=%lookup(*blanks:stora);
stora(ix)=rlstor;
endif;
read alcorl;
enddo;
tend=%timestamp;
except rad;
*inlr=*on;
/end-free
Oqsysprt e rad 3
O tstart 26
O tend 60
O tcount 100
2/"FETCH" program
Fqsysprt o f 120 printer
D tstart s z
D tend s z
d stora s like(rlstor) dim(999)
D ix s 5 0
D tcount s 7 0
d dsalcorl e ds extname(calcorl)
/free
exec sql declare csr01 cursor for select * from calcorl;
exec sql open csr01;
tstart=%timestamp;
exec sql
fetch next from csr01 into :dsalcorl;
dow sqlstt = *zeros;
tcount=tcount+1;
if %lookup(rlstor:stora)=*zero;
ix=%lookup(*blanks:stora);
stora(ix)=rlstor;
endif;
exec sql
fetch next from csr01 into :dsalcorl;
enddo;
tend=%timestamp;
except rad;
*inlr=*on;
/end-free
c/exec sql
C+ CLOSE CSR01
C/END-EXEC
Oqsysprt e rad 3
O tstart 26
O tend 60
O tcount 100
Med vänlig hälsning / Best regards
Åke Olsson
Pdb DataSystem AB
Box 433 SE 551 16 Jönköping Sweden visit: Brunnsgatan 11
phone: +46 (0)36 342976 mobile: +46 (0)705 482976 fax: +46 (0)36 34 29
29
ake.olsson@xxxxxx <mailto:ake.olsson@xxxxxx> www.pdb.se
This e-mail and any attachments may contain confidential and
privileged information. If you are not the intended recipient, please
notify the sender immediately by return e-mail, delete this e-mail and
destroy any copies. Any dissemination or use of this information by a
person other than the intended recipient is unauthorized and may be
illegal.
--
This is the RPG programming on the AS400 / iSeries (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.
--
This is the RPG programming on the AS400 / iSeries (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.
############################################################################
#########
This correspondence is for the named person's use only. It may contain
confidential or legally privileged information, or both. No confidentiality
or privilege is waived or lost by any mistransmission. If you receive this
correspondence in error, please immediately delete it from your system and
notify the sender. You must not disclose, copy or rely on any part of this
correspondence if you are not the intended recipient. Any views expressed in
this message are those of the individual sender, except where the sender
expressly, and with authority, states them to be the views of Veda
Advantage. If you need assistance, please contact Veda Advantage on either
:- Australia 133124 or New Zealand +64 9 367 6200
--
This is the RPG programming on the AS400 / iSeries (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.
--
This is the RPG programming on the AS400 / iSeries (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.
--
This is the RPG programming on the AS400 / iSeries (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.
--
This is the RPG programming on the AS400 / iSeries (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
