How to prevent SQLServer JDBC Query with XML type caching entire resultset at query?

Question

I have a query which generates a fairly large XML document (~30k) as a query column for each record of a large table, of the form...

SELECT recordKey, lastUpdatedDate, ( SELECT ... FOR XML PATH( 'elemName' ), TYPE )
FROM largeTable
ORDER BY lastUpdateDate

If I run this query from SQLServer management studio, the query returns almost instantly, showing the first rows, as I would expect, and continues to run in the background.

However, when I run this query from the Camel JDBC component in StreamList mode, it appears to cache the entire resultset at the point of querying, which means I run out of memory.

I've checked the JDBC driver properties, and explicitly set the responseBuffering property to adaptive, and have also tried setting the selectMethod to cursor, neither of which appear to make any difference to my query.

Is this a characteristic of querying XML with JDBC, or are there some parameters I need to set differently?

Answer 1

However, when I run this query from the Camel JDBC component in StreamList mode, it appears to cache the entire resultset at the point of querying, which means I run out of memory.

camel-sql introduce the 'StreamList' output type in v2.18.x. Since you are using v2.17.6, your configuration may be falling back to 'SelectList' (default value). This causes the load of whole result set as list in memory. Having xml type in your query/result set doesn not have any influence on this behavior.

You may find this code at: org.apache.camel.component.sql.SqlConsumer.poll()

I suggest you to upgrade camel-sql version to v18.x ( or latest)

Hope this helps.