2 Replies Latest reply: Aug 13, 2012 8:57 AM by 955381 RSS

    Performance of XSLT transformation

    955381
      Hello everybody,

      I´m trying to do some in-database XML transformations from one XML format to another with a rather complex xsl stylesheet of about 9000 lines of code which performs this transformation. At first I tested the XDK XSLT functionality by simply doing a Transformation over the JAXP API outside the Database. It performed not bad, altough the memory consumption was a little bit higher then Saxon or Xalan. After that I read about the possibilities of in-database transformation, which should consume less memory because of oracle´s xml features like scalable DOM, XML Indexing and Query Rewriting when using object relational or binary storage. So we loaded our testdata into the db (we are using 11gR2 on a test system with Intel Core2Duo @ 3GhZ and 3GB RAM), in different XML storage options (XMLTYPE CLOB, Binary, OR) and tried to do a XSLT transformation on the columns. But it isn´t working.

      The db seems not to be able to cope with the transformation although the parser and processor should be the same as the off-database transformation that was working well (or am I wrong?). After half an hour we aborted because the db hadn´t finished the transformation yet (the off-db transformation of this 100KB XML took less then a second). We tried indexing the columns with XMLIndex, didn´t work out, we tried both XMLTYPE´s XMLtransform() and dbms_xslprocessor.processxsl(), same problem, the DB is working and working with no result, after some time we have to terminate. With a simple teststylesheet the transformation is doing fine but our complex stylesheet seems to be the problem.

      My question is, why is the xslt performance so poorly with the complex stylesheet and why can I use the XDK Parser and Processor in an off-db transformation very efficiently but when doing the same transformation in the db it´s not working.

      Hope you can help me out a little bit I´m really running out of ideas.

      Greetz dirac
        • 1. Re: Performance of XSLT transformation
          odie_63
          Hi,
          The db seems not to be able to cope with the transformation although the parser and processor should be the same as the off-database transformation that was working well (or am I wrong?).
          No, the database built-in XSLT processor (C-based) is not the same as the XDK's Java implementation.
          There's also a C XSLT processor available for external transformation and working with compiled stylesheets, it should outperform the Java implementation.
          See : http://docs.oracle.com/cd/E11882_01/appdev.112/e23582/adx_c_xslt.htm#i1020191
          We tried indexing the columns with XMLIndex, didn´t work out, we tried both XMLTYPE´s XMLtransform() and dbms_xslprocessor.processxsl(), same problem, the DB is working and working with no result, after some time we have to terminate. With a simple teststylesheet the transformation is doing fine but our complex stylesheet seems to be the problem.
          XMLIndexes won't help at all in this situation.
          In my experience so far with the built-in engine, I've seen the best result when the XML document we want transform is stored as binary XML.
          I don't know about the scalable DOM (or "lazy" DOM loading) feature described in the documentation, seems it doesn't support complex transformations, much like functional vs. streaming evaluation for XQuery.
          Hope you can help me out a little bit I´m really running out of ideas.
          I'd be interested in a test case if you have the possibility (stylesheet + sample input XML). You can mail me at this address : mb[dot]perso[at]wanadoo[dot]fr
          If you can't, could you describe what kind of transformation you're performing and how can I reproduce that behaviour?

          Thanks.

          Marc.
          • 2. Re: Performance of XSLT transformation
            955381
            Hi Mark,
            thanks for your answer but I´m afraid I can´t give away the stylesheet. What it does is mapping an ONIX for Books Product Information Message to our inhouse XML format for storage and further processing. It´s conforming to the XSLT 1.0 Standard, so there´s no fancy stuff just a lot of testing, copying to output and counting. If I knew how to reproduce this behaviour, I would know what causes it and could solve it, but that´s my problem, hehe. It´s just a simple transformation, even XSLT processors which haven´t been further devloped since a long time like XT are able to cope with the stylesheet efficiently.

            By the way another problem was the ONIX schema, which is without adjustment not applicable for registering in xmldb. When creating an OR XMLTYPE table it produces an ORA-01792 because it trys to generate over 1000 columns in one single table, nasty nasty.