如何防止使用XML类型的SQLServer JDBC查询在查询时缓存整个结果集? [英] How to prevent SQLServer JDBC Query with XML type caching entire resultset at query?

问题描述

我有一个查询，该查询生成一个相当大的XML文档(〜30k)作为大表格的每个记录的查询列，格式为...

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

如果我从SQLServer Management Studio运行此查询，该查询几乎立即返回，并显示了我所期望的第一行，并继续在后台运行.

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.

但是，当我以StreamList模式从Camel JDBC组件运行此查询时，它似乎在查询时缓存了整个结果集，这意味着我内存不足.

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.

我已经检查了JDBC驱动程序属性，并将responseBuffering属性显式设置为adaptive，并且还尝试将selectMethod设置为cursor，但这两个都不会对我的查询产生任何影响

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.

这是使用JDBC查询XML的特征，还是我需要对某些参数进行不同的设置?

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

推荐答案

但是，当我从Camel JDBC组件中运行此查询时 在StreamList模式下，似乎在该点缓存了整个结果集 的查询，这意味着我的内存不足.

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引入了'StreamList'

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()

我建议您将camel-sql版本升级到v18.x(或最新版本)

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

希望这会有所帮助.

这篇关于如何防止使用XML类型的SQLServer JDBC查询在查询时缓存整个结果集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！