[Exist-open] Counter-intuitive performance results with transform:transform() - Saxon

[Len S. <le...@wi...>]

Hello eXisters,

I was running some simple performance benchmarks to decide how best to optimize a new app, and ran into an unexpected result.

I wanted to see how much faster it was to read a large XML file from the db and pass to Saxon compared to dynamically generating the XML file from an SQL query.  I assumed  the latter would be slower, and I wanted to quantify how much.  But to my surprise, generating the XML file dynamically was an order of magnitude faster in Saxon.

To setup the test, I created big.sql.xml in the database by running the SQL query:

let $big := sql:execute($connection, 'SELECT * FROM lists LIMIT 30000', fn:true())
return xmldb:store('/db/', 'big.sql.xml', $big)

This took 61.8s, with 3.1s for sql:execute() and 58.2s for xmldb:store().  The file size in the database is 167MB.  I don’t care about the slow store time, as this is just a test.

First, I did a simple transform using the saved file:

let $xsl := doc('/db/test.xsl')
let $big := doc('/db/big.sql.xml')
return transform:transform($big, $xsl, ())

This took 8.9s, with 8.9s for transform:transform().

Second, I dynamically ran the SQL query, passing that into the transform:

let $xsl := doc('/db/test.xsl')
let $big := sql:execute($connection, 'SELECT * FROM lists LIMIT 30000', fn:true())
return transform:transform($big, $xsl, ())

This took 4.0s, with 3.1s for sql:execute() and 0.9s for transform:transform().

I was expecting the 3.1s to be added, which it was.  But I was not expecting transform:transform() to drop from 8.9s to 0.9s, a 90% reduction in execution time.  So I’m missing something fundamental about how this works.  Obviously, I’d love to get the 0.9s performance in the first case, while replacing the 3.1s of sql:execute() time with what I assumed would be a much faster doc().

Any insight?

--len

FYI, the simple transform in test.xsl is:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:sql="http://exist-db.org/xquery/sql" exclude-result-prefixes="sql" version="3.0" expand-text="yes">
<xsl:output method="xml" version="1.0" encoding=“utf-8" indent="yes"/>
<xsl:mode on-no-match="shallow-skip"/>

<xsl:template match="/sql:result">
<test>
<xsl:sequence select="@*"/>
</test>
</xsl:template>

</xsl:stylesheet>