RE: How to transform flat structure into hierarchical one?

["Paulo Gaspar" <Paulo dot Gaspar at krankikom dot de>]

Sorry Steve, but that is not the usual case with ADO. ADO
only generates redundant network trips when the shape is a
"parameterized hierarchy".

You can find a nice introductory text that refers that at:
http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/dasdk/tech0a
p1.htm

One of its pages mentions the network trip issue:
http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/dasdk/tech05
mh.htm

Quoting:
"A parameterized hierarchy is similar to a relation-based
hierarchy. The main difference is that instead of downloading
all the records for both the parent and the child recordsets,
the parents are downloaded and then the child records are
downloaded as they are accessed by the consumer application."


With a ADO SHAPE "relation-based hierarchy", each SQL
statement is executed once and the returned datasets is
"shaped" in memory.

Considering this example:

  SHAPE
      {   SELECT DISTINCT
                  RubrikKey,
                  PaperCatOrder AS CatOrder,
                  RubrikCaption AS Caption
              FROM atbCatalogRubrik
      }
  APPEND
      (   {   SELECT DISTINCT
                      RubrikKey,
                      ProductLineKey      AS ProdLineKey,
                      PaperCatOrder       AS CatOrder,
                      ProductLineCaption  AS Caption
                  FROM atbCatalogProductLine
          } AS ProdLine
          RELATE RubrikKey TO RubrikKey
      )

The 2 select statements will be executed (once each) and ADO
will group the data in memory in the following way:
 - The results of the master select (over the "atbCatalogRubrik"
   table populate the resulting recordset;
 - A nested recordset field named "ProdLine" will be added to
   the ones listed in the first select statement;
 - For each record, the field "ProdLine" will be populated with
   the corresponding detail fields (from the second "SELECT")
   according to the rule defined by the "RELATE" clause.
   In this case, for a given master record the field "ProdLine"
   will be populated with all the records returned by the 2nd
   "SELECT" for which their "RubrikKey" field matches that
   master record's "RubrikKey" field.

It works for fields with different names and for multiple
fields  too.


A recent project's experience ensures that it really works this
way. I have a routine to extract all catalog data I need from a
database into XML. Some numbers:
 - It is a SHAPE involving 10 SQL statements;
 - 3 of them are for data dependent on products and there are
   around 1800 products;
 - Around 2 MB of XML are generated (in ISO-8859-1);
 - The all process takes less than 13 to 14 seconds, and the
   last couple of seconds are spent generating and exporting
   the XML. It takes 10 to 11 seconds to build the data
   hierarchy in memory.

This is consistent with the execution of 10 SQL statements. And
it is still quite fast - many of the database engines/interfaces
I used in the pass would take much more time.

But, for this specific case, if ADO would execute one statement
per dependency, it would go quite over 7000 statements - 7200 is
a minimum I can track at this moment but my guess is that the
real number would exceed 8000.

But 7000 is enough. No way that ADO (even being fast) would
perform those 7000 "individual data cursors and trips across the
networks" in less than 15 seconds!!!


What is better is that this is a service supported by a "data
provider" layer, that stays between the ADO recordset and any
other data provider. You can use it with any database. I already
tried ADO Shaping with:
 - MS SQL Server;
 - MS Access;
 - Oracle 8i.


I am not advertising a M$ product. I am advertising a very
efficient and elegant solution they implemented.

The reason I argue so enthusiastically about it is that I would
like to see this kind of solution implemented by Oracle on
Oracle's, Java libraries.
 - One query per SQL statement;
 - Efficient merging using recordsets;
 - Exporting to XML afterwards if you want, otherwise work the
   data some other way.

This is just natural for Oracle: having nested tables in the
database and nested recordsets in memory. And I think it would
be more efficient and flexible than have the developers doing
it with XSLT.


There are many other interesting solutions to learn from ADO.
ADO as nothing to do with the awful DAO. Much more than the
order of the characters changed.


Ok, ok, ok... I know you are a 1st a XML guy... but since I
red also that you are "Consulting Product Manager, Business
Components for Java & XSQL Servlet Development Teams"... may
be you could tell them to take a look on ADO huh?

I am starting to work with Oracle and I like easy things.
=;o)


Thanks for the patience (if you reached this line).


Have fun,

Paulo
(paulo.gaspar@krankikom.de)



> -----Original Message-----
> From: owner-xsl-list@mulberrytech.com
> [mailto:owner-xsl-list@mulberrytech.com]On Behalf Of Steve Muench
> Sent: Thursday, June 08, 2000 17:01
> To: xsl-list@mulberrytech.com
> Subject: Re: How to transform flat structure into hierarchical one?
>
>
> | Unfortunately, Steve discovered this *after*
> | Mike Kay's book came out.
> | .......
>
> Jenny,
>
> I bumped into this idea while writing up a section in
> my forthcoming book on Building Oracle XML Applications
> for O'Reilly, where I was trying to understand and
> subsequently explain the cases where using the database
> to do the data "shaping"/grouping was best and where
> doing a single query and allowing XSLT to do the grouping
> was best.
>
> The data "shaping" techniques supported in Microsoft ADO
> and in Oracle using nested CURSOR() expressions end up
> achieving the result using many individual data cursors and
> trips across the networks. Depending on the amount of
> data this might be too much network traffic.
> ..........
>
> ______________________________________________________________
> Steve Muench, Lead XML Evangelist & Consulting Product Manager
> Business Components for Java & XSQL Servlet Development Teams
> Oracle Rep to the W3C XSL Working Group
> Author "Building Oracle XML Applications", O'Reilly, Oct 2000


 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list