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

user_2198651 0
Viewed 267

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?

jdbc sql-server
2 Answers

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.

user_17004670

Not sure if it is possible or desirable in your application but in any case... to load all contents for a table is something that we have to prevent when possible. So, I will propose you an alternative by obtaining pages:

DECLARE @RowsPerPage    AS INT = 5;
DECLARE @CurrentPage    AS INT = 3;
SELECT recordKey, lastUpdatedDate, ( SELECT ... FOR XML PATH( 'elemName' ), TYPE )
FROM largeTable
ORDER BY lastUpdateDate
    OFFSET (@CurrentPage-1)* @RowsPerPage ROWS
    FETCH NEXT @RowsPerPage ROWS ONLY;

Just change the parameters to set your desired Rows Per Page and Current Page. I hope it helps.

user_108145030
Related