SQLServerの2100個問題のひとつの解決策として、テーブル値パラメーターを利用する

useridを主キーとして、ユーザの情報を格納する、以下のようなテーブルがあるとします。

CREATE TABLE users (
  userid   INT           NOT NULL PRIMARY KEY, 
  username NVARCHAR(100) NOT NULL
);

複数のuseridがあたえられて、その情報をusersテーブルから取得したいという場合、以下のようなクエリになると思います。

SELECT *
FROM users
WHERE userid IN (?, ?, ? ...)

**問題は、SQLServerではこのプレイスホルダが2100個を超えるとエラーになることです。**たとえば、以下のようなコードで上記のようなクエリを動的に生成したとします。

int[] userids = ...;

// SELECT * FROM users WHERE userid IN (?, ?, ?) のようなクエリを構築する。
String sql = "SELECT * FROM users WHERE userid IN (";
sql += Arrays.stream(userids).mapToObj(unused -> "?").collect(Collectors.joining(","));
sql += ")";

PreparedStatement stmt = con.prepareStatement(sql);
for (int i = 0, len = userids.length; i < len; i++) {
    stmt.setInt(i+1, userids[i]);
}

ResultSet rs = stmt.executeQuery();
while (rs.next()) {
    int userid = rs.getInt("userid");
    String username = rs.getString("username");
    doSomething(userid, username);
}

ここでuserids配列に格納されている件数が2100を超えていると、以下のようなエラーが発生します。

com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1662)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3488)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:237)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:456)
	at neko.the.shadow.sqlserver_sample.App.main(App.java:34)

アプリケーションの規模にもよりますが、プレイスホルダが2100個程度あるクエリを発行することは珍しくもなんともなく、この2100個問題はSQLServerを利用するシステム開発では必ずと言ってよいほど頭をもたげてきます。

前置きが長くなりましたが、**この2100個問題のひとつの解決策として、テーブル値パラメーターを利用する方法があります。**上記のユースケースでは、まず以下のようにして、テーブル値パラメーターの型を作成しておきます。

CREATE TYPE useridTableType AS TABLE (userid INT);  

あとは以下のようにして動的クエリを発行します。

int[] userids = ...;

SQLServerDataTable useridTable = new SQLServerDataTable();
useridTable.addColumnMetadata("userid", java.sql.Types.INTEGER);
for (int userid : userids) {
    useridTable.addRow(userid);
}

String sql = "SELECT * FROM users WHERE userid IN (SELECT userid FROM ?)";
SQLServerPreparedStatement  stmt = (SQLServerPreparedStatement)con.prepareStatement(sql);
stmt.setStructured(1, "useridTableType", useridTable);

ResultSet rs = stmt.executeQuery();
while (rs.next()) {
    int userid = rs.getInt("userid");
    String username = rs.getString("username");
    doSomething(userid, username);
}

これだとuseridTableに格納されている値が2100を超えていてもエラーにはなりません。やったね(´・ω・`)