Teiid XML functions performance

[jrod2016]

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

[shawkins]

> 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.

[jrod2016]

Thanks for your response.