2 Replies Latest reply on Sep 9, 2016 9:21 AM by John Rodrigues

    Teiid XML functions performance

    John Rodrigues Novice

      I have a Teiid view definition where I use XML table to produce tabular output. My view definition is listed below:

       

             <metadata type="DDL">
                  <![CDATA[CREATE VIEW View1 (Product_Name VARCHAR(102), SalesDate TIMESTAMP, Unit_Price DOUBLE) AS SELECT A.Product_Name, A.SalesDate, A.Unit_Price FROM (EXEC AdfcoreSource.executeProfile(101)) AS f, XMLTABLE('//*[local-name()=''dataset'']/*[local-name()=''data'']/*[local-name()=''row'']' PASSING XMLPARSE(DOCUMENT f.result WELLFORMED) COLUMNS Product_Name VARCHAR(102) PATH '*[local-name()=''value''][1]/text()', SalesDate TIMESTAMP PATH '*[local-name()=''value''][2]/text()', Unit_Price DOUBLE PATH '*[local-name()=''value''][3]/text()') AS A]]>
              </metadata>
      

       

      Focusing on the SELECT portion of the view definition, f.result is a stream returning XML (which is guaranteed to be well formed).

       

      SELECT A.Product_Name, A.SalesDate, A.Unit_Price 
      FROM (EXEC AdfcoreSource.executeProfile(101)) AS f, 
      XMLTABLE('//*[local-name()=''dataset'']/*[local-name()=''data'']/*[local-name()=''row'']' 
      PASSING XMLPARSE(DOCUMENT f.result WELLFORMED) 
      COLUMNS Product_Name VARCHAR(102) PATH '*[local-name()=''value''][1]/text()', SalesDate TIMESTAMP PATH '*[local-name()=''value''][2]/text()', 
      Unit_Price DOUBLE PATH '*[local-name()=''value''][3]/text()') AS A;
      

       

      I assume that specifying WELLFORMED will speed things up, since validation is skipped. I want to know what other tweaks I can make to achieve maximum performance of the Teiid specific portions of the select.

       

      So, should I use

      XMLPARSE(CONTENT f.result WELLFORMED) instead of XMLPARSE(DOCUMENT f.result WELLFORMED). I'm not too clear on the difference between DOCUMENT vs CONTENT from the documentation: (XML Functions - Teiid 9.0 (draft) - Project Documentation Editor )

       

      Will I get better performance if I use a TEXTTABLE instead of a XMLTABLE?

       

      Thanks,

      John

        • 1. Re: Teiid XML functions performance
          Steven Hawkins Master

          > I assume that specifying WELLFORMED will speed things up, since validation is skipped

           

          That is correct.

           

          > I want to know what other tweaks I can make to achieve maximum performance of the Teiid specific portions of the select.

           

          The performance will largely be determined by the size of the input document and the XMLTABLE xquery/xpath.  See XQuery Optimization · Teiid Documentation

           

          If possible not using the descendant axes // in the context path will allow the processing to use streaming, which can greatly reduce latency/overhead.

           

          > I'm not too clear on the difference between DOCUMENT vs CONTENT from the documentation

           

          CONTENT allows for anything that could appear under an element - including a sequence, or even text. DOCUMENT requires that you are parsing starting at the document root / single element.

           

          > Will I get better performance if I use a TEXTTABLE instead of a XMLTABLE?

           

          TEXTTABLE will only be useful for fixed/delimited text.

          • 2. Re: Teiid XML functions performance
            John Rodrigues Novice

            Thanks for your response.