如何解决Java执行SQL时的SQLServerException资源耗尽问题？

我通过java.sql.Statement.executeQuery()执行以下SQL查询：

SELECT columns  FROM Table1, Table2
WHERE ( ColX IN (...) OR ColX IN (...) OR ... OR ColX IN (...) )
  AND Table1.Col1 = Table2.Col2

该查询包含89个IN子句，除最后一个包含476个整数ID外，其余每个IN子句均包含1000个整数ID，总计89476个整数ID。

执行后返回错误：

com.microsoft.sqlserver.jdbc.SQLServerException: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

已知该查询在SQL Server Management Studio中可正常运行并返回1937行结果，也了解可以通过临时表（#temp table）或表值参数（Table-Valued Parameters）解决，但由于是旧版本产品，需要最小改动的方案。当前使用SQL Server 2022，暂不清楚客户的数据库版本。

核心问题：是否可通过调整配置或参数，让该查询在Java中正常执行？

一、调整SQL Server数据库配置 #

1. 增大查询内存限制

   • 执行以下命令提升查询可使用的内存阈值（需管理员权限）：

     sp_configure 'show advanced options', 1;
     RECONFIGURE;
     sp_configure 'query memory grant limit (MB)', 4096; -- 可根据实际情况调整，比如设置为4GB
     RECONFIGURE;
     

   • 注意：不同SQL Server版本的参数名称可能略有差异，需确认对应版本的配置项。

2. 启用查询优化器低内存模式

   • 对于SQL Server 2016及以上版本，可在查询开头添加优化提示：

     OPTION (USE HINT('FORCE_MEMORY_GRANT_SCALING'))
     

   • 该提示会让查询优化器在内存不足时调整执行计划，降低内存占用。

二、调整JDBC连接参数 #

1. 使用服务器端游标

   • 创建Statement时指定服务器端游标，避免客户端加载大量数据导致的内存压力：

     Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
     

   • 或直接在JDBC URL中添加参数：useServerSideCursor=true

2. 增大数据包大小

   • 在JDBC URL中设置更大的数据包大小，减少网络交互次数：

     jdbc:sqlserver://yourserver:1433;databaseName=yourdb;packetSize=32767
     

   • 注意：数据包大小不能超过SQL Server配置的max packet size（默认4096KB），需先确认数据库端配置。

3. 强制简单参数化

   • 在JDBC URL中添加forceParameterization=true，让SQL Server对查询进行简单参数化，可能降低执行计划生成压力。

三、最小代码改动方案：拆分查询批次 #

把原查询拆分为多个小批次（比如每20个IN子句为一个查询），在Java中循环执行每个批次查询后，在客户端合并结果。这种方式无需修改数据库配置，代码改动量极小。

注意事项 #

• 所有配置调整需测试验证，尤其要注意不同SQL Server版本的兼容性，部分提示或配置项在2012及以前的旧版本可能不支持。
• 若客户使用较旧的SQL Server版本，拆分查询批次是更稳妥的最小改动方案。

内容的提问来源于stack exchange，提问作者LarryM