RE: SQL vs native access

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: Walden H. Leverich III
> 
> >From: Joe Pluta [mailto:joepluta@xxxxxxxxxxxxxxxxx]
> >Do you have any type of formal information on this.
> 
> Look at http://www.redbooks.ibm.com/redpieces/pdfs/sg246598.pdf,
> specifically figure 2-2 on page 12.
> 
> >or are you just guessing?
> 
> Guessing, no. Do I have a complete understanding of the 5.2 changes,
NO!
> I'm
> just beginning to comprehend the red piece above.

Thanks for the link.  The document is actually pretty straightforward.
Basically, IBM have implemented a sub-SLIC SQL Query Engine (SQE) that
should enhance queries, in some cases significantly, by moving much of
the work of a complex query under the covers.  Temporary objects will be
far more ephemeral, and will take advantage of the single level store
characteristics of the iSeries (which should in turn make them perform
bonzo compared to other architectures).  There are also SMP
enhancements.

This doesn't go without a cost.  The SQE is only invoked for certain
queries.  Those that are not processed by the SQE are handed back to the
CQE (the traditional, "Classic" Query Engine).  In fact, this handoff
can even add overhead to the process.

So what kinds of queries require the CQE?  Well, pretty much everything
of any complexity outside of queries.  INSERTs, UPDATEs, DELETEs all run
through the CQE.  Views.  Subqueries.  Derived tables.  LIKE or UNION.
If you have a logical file with SELECT/OMIT, or RENAME, or ALTSEQ, it
requires the CQE. LOBs require the CQE.

So what are we saying here?  Basically as far as I can tell, the SQE
enhancements basically make SQL run really well for what it was
originally intended to do: query data.  And it has little effect on
those cases where you try to replace traditional native I/O with SQL; in
fact, it has a slight negative impact in those cases.

No, I don't see anything in this document suggesting tat single record
FETCHes will outperform CHAIN any time soon.

Which makes perfect sense to me: upgrade SQL to be the fastest possible
ad hoc query mechanism on the planet, and use native I/O for business
logic.

Joe