Which query? Sorry if I'm getting confused. Is this proof that the SQL query needs to be different or that - no matter what SQL you use - that amount of bind variables is problematic on SQL Server?

The SQL query needs to be different for efficiency sake. It doesn't appear to be the volume of bind parameters because when I rewrote the solution to use the Exists In Subquery Values clause, the same number of bind parameters are used as the original SQL query, but runs executes considerably faster (25-40ms versus 6000-7500ms).

I pin-pointed the issue in CriteriaObjectsInitializer, the method initializeObjects uses a list of the entity IDs to populate a disjunction of IN expressions, where the number of IDs included within an IN expression is capped at 500. Whether I provide my own criteria or whether I allow the CriteriaObjectsInitializer to create it's own, it is the appended disjunction of the IN expressions that is problematic.

For SQL Server, the criteria's output on SQL server becomes:

 WHERE ( (ITEM_ID = ? AND PLANT_ID = ?) OR (ITEM_ID = ? AND PLANT_ID = ?) OR ... <more composite key pairs> )

When I tested other database platforms, that is where I noticed Oracle 9i's generated SQL and sought to find whatever was considered SQL Server 2008's equivalent because Oracle 9i was performing so well. That is where I stumbled across the new Exists In Subquery Values clause for SQL Server 2008 and 2012.

The InExpression criterion is designed to check whether the dialect supports supportsRowValueConstructorSyntaxInInList or if the number of columns being expressed for the clause is a single field regardless of dialect. In the case of where the entity's ID isn't a composite or where the dialect supports supportsRowValueConstructorSyntaxInInList = true regardless of the entity ID's field count, the output SQL is:

 WHERE (ITEM_ID,PLANT_ID) IN ((?,?),(?,?),(?,?),... up to page size pairs)