RE: SQL within RPG

Yes and the CQE is a performance nightmare.... 


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of HauserSSS
Sent: Tuesday, November 15, 2005 12:03 AM
To: RPG programming on the AS400 / iSeries
Subject: AW: SQL within RPG

Hi,

the decision of the optimizer, if and which access path is used, is also
affected by the data combination.
The statistics manager collects these information and uses them to find
the
optimal access path.

@Tommy,

what happened if you change the 2 columns in the group by clause?
I assume that in the first column are only a few distinct values.
This may cause the optimizer to prefere a table scan.
In the second example the result must be sorted in any case.
So the optimizer may decide using an access path is less expensive than
generating a result set and sorting this result set after.

But all in all that are speculations.
Without knowing the data combination and the statistics in detail,
we only can guess and try to understand what happened.

... and there may be also differences depending on the query engine
used.
It may be that executing a select statement with CQE (Classic Query
Engine)
an access path is used.
Executing the same select statement on the same data combination with
SQE
(Standard Query Engine) a table scan will be preformed (and vice versa).

Keep in mind, CQE is always used if a logical file with SELECT/OMIT
clause
is created over the physical file.
CQE is also used if a logical file (not SQL-View) is specified in the
select
statement.

Birgitta

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Joe Pluta
Gesendet: Montag, 14. November 2005 21:42
An: 'RPG programming on the AS400 / iSeries'
Betreff: RE: SQL within RPG


CRAP SHOOT CRAP SHOOT!

<grin>

Actually, I just had a related problem.  The exact same data in the
exact same file in (as far as I can tell) the exact same order on two
different machines.  The same SQL statement without an ORDER BY returned
the data differently on the two machines.

The SELECT criteria should have caused the SQL engine to use the same
index (it was a keyed physical file and the selection fields were the
first three of six fields in the key), but until I added an ORDER BY
over all six fields, I was not getting the same sequence on the two
machines.

Joe

> From: Holden Tommy
>
> Question is is there any trade off by specifically declaring the ORDER
> BY in conjunction with the GROUP BY.  I'd prefer to know beyond the
> shadow of a doubt that my data will be in the order I desire.  Should
> someone (for whatever reason) delete/modify that logical view what
would
> happen then?  Would you still receive the same data order as you were
> expecting or would it be a "crap shoot"???

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