Hi,
Just a few information:
1. With Release V5R2 IBM started to revamp the current query engine, which
was unchanged since introduction, i.e. the SQL query engine was introduced.
To guarantee, that all queries can be executed the SQE was introduced step
by step. With each releases more functionality will be taken over by the
SQE. But until now (release V5R4) the introduction of the SQE is not yet
finished, for example queries where scalar functions like UPPER or LOWER are
used, or the sort sequence is changed from hex to anything else will be
executed by the old query engine. Also the SQL query engine has no way to
resolve specified DDS described logical files, that means the query must be
executed by the classic query engine. Until now the SQE cannot be used if
there are logical files with select/omit clauses, specified over the base
tables. To get the SQE to be used for those tables, the option
INGORE_DERIVED_INDEX in the QAQQINI file must be set to *YES. As soon as
these option is set, access paths stored in those logical files are no
longer considered by the query optimizer, i.e. it may be necessary to create
additional indexes (or DDS desrcibed logical files with the appropriate key,
but without select/omit clauses):
2. The SQL query engine is written in OO-design and can handle more flexible
than the classic query engine.
Let's assume, there is a check constraint that only allows 'X' and 'Y' in a
column. If you execute a SQL statement where you specify where Field = 'A',
SQE will immediately return no row, while the query will be executed
completely before the result of zero rows will be returned (Constraint
awarness). An other example would be, two tables are joined, but you only
use information from one of the tables and the second is not needed. SQE
will only use this tables, while CQE will join all rows and select from the
joined rows. There are a lot of other examples where the SQE will handle
more efficient.
3. There are a lot of other advantages of the SQE. For example all access
plans for queries executed by the SQE are stored in a systemwide available
SQE Plan Cache. Before executing a query an access plan must exist. (An
access plan contains all information about how ot access the data, i.e.
which indexes will be used, which temporary objects must be built ...).
Queries executed by the CQE can only use access plans either stored in the
(service-)programs, in SQL packages or in the job's cache. The access plan
will only be stored in the (service-)program objects for static SQL, for
dynamic SQL the access plan must be created by scratch, at leas the first
time the query will be executed in a job. With the SQE the SQE plan cache is
checked first for an appropriate access plan. In this way the optimization
process for dynamic SQL can be reduced.
4. Both query engines analyse the existing indexes and DDS described logical
files to find the optimal access paths. But there are differences how the
indexes are checked. CQE checks the last created access plans (indexes or
logical files) the first. If there are a lot of access paths created over
the base tables, the CQE optimizer may time out before it found the optimal
index. With the SQE the access path to check are presorted depending on the
joins, where clauses and group bys specified int the SQL statements. The SQE
stops analyzing the access paths as soon as an access path is find that is
less optimal than the preceeding one.
5. CQE cannot use any statistics, i.e. to find the optimal index only
estimations are used. For example when an = sign is used the CQE estimates
that 10% of all rows will be returned. But if most of your orders, lets say
80%, are from a single client, using a table scan to find all those orders
will be more efficient than using an index. SQE can handle these situation
by inquiring the statistics.
6. When executing queries, CQE tends to create temporary tables that will be
filled with data, i.e. data must be loaded into memory and after the desired
rows will be selected. SQE on the other hand prefers to use temporary data
structures or relative record lists, i.e. first loads a minimum information
and retrieves the complete data in the last moment.
7. All new features introduced with release V5R3 (such as EXCEPT or
INTERSECT) and release V5R4 (such as OLAP ranking functions, recursive
common table expressions) can only be executed by the SQE.
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: midrange-l-bounces@xxxxxxxxxxxx
[
mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Pantzopoulos,
Michael
Gesendet: Monday, May 07, 2007 04:24
An: midrange-l@xxxxxxxxxxxx
Betreff: More SQE CQE Theory.
I'm still curious about the relationship between SQE & CQE
(
http://archive.midrange.com/midrange-l/200705/msg00066.html). Can someone
please comment on the validity of the following perceptions (of mine)?
* In all that I've read, I thought that the goal of SQL was to
have it run by the SQE and not the CQE.
* The reason for this is that the SQE uses indexes to make the
resolution of the SQL more efficient.
* However, at the moment there are a number of conditions
(reducing with every OS upgrade) under which the SQL will be routed to the
CQE.
The discussion in the earlier topic implied that there was no point in
getting hung up about CQE & SQE, implying that it didn't really matter. That
really confused me because of the amount of literature being generated on
SQL for the iSeries.
I guess I've been under the impression that larger efficiencies can be made
by using a good indexing strategy. But if the indexes can't be used because
of the despatch conditioning, then there's no point.
In other words, unless we can overcome the conditions that switch the SQL to
the CQE, there's no point in worrying about indexing.
Unless of course the CQE has some capacity to be managed by the
developer. Or is the CQE aware of logical file indexes?
Is this a reasonable 'newbie' summary?
Regards,
"This e-mail and any attachments to it (the "Communication") is, unless
otherwise stated, confidential, may contain copyright material and is for
the use only of the intended recipient. If you receive the Communication in
error, please notify the sender immediately by return e-mail, delete the
Communication and the return e-mail, and do not read, copy, retransmit or
otherwise deal with it. Any views expressed in the Communication are those
of the individual sender only, unless expressly stated to be those of
Australia and New Zealand Banking Group Limited ABN 11 005 357 522, or any
of its related entities including ANZ National Bank Limited (together
"ANZ"). ANZ does not accept liability in connection with the integrity of or
errors in the Communication, computer virus, data corruption, interference
or delay arising from or in respect of the Communication."
midrange.com
