|
|
bq. 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:
{code}
WHERE ( (ITEM_ID = ? AND PLANT_ID = ?) OR (ITEM_ID = ? AND PLANT_ID = ?) OR ... <more composite key pairs> )
{code}
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:
{code:title= Oracle 9i Single Field }
WHERE FIELD IN ( ?, ?, ?, ?, ... up to page size keys)
{code}
{code:title=supportsRowValueConstructorSyntaxInInList concept}
WHERE ( ITEM_ID,PLANT_ID) IN ((?,?),(?,?),(?,?),... up to page size pairs)
{code}
The former is obviously supported on any single SQL Server platform; however the later isn't.
So what I did was the following:
{code:title=Dialect.java}
public boolean supportsMultiColumnInMarshalledAsExistsWithSubqueryValues() {
return false;
}
{code}
{code:title=SQLServer2008Dialect.java}
@Override
public boolean supportsMultiColumnInMarshalledAsExistsWithSubqueryValues() {
return true;
}
{code}
{code:title=InExpression.java}
public String toSqlString( Criteria criteria, CriteriaQuery criteriaQuery )
throws HibernateException {
if ( criteriaQuery.getFactory().getDialect()
.supportsRowValueConstructorSyntaxInInList() || columns.length<=1) {
/* do the default stuff it has been doing before */
}
else if( criteriaQuery.getFactory().getDialect().supportsMultiColumnInMarshalledAsExistsWithSubqueryValues() )
{
/* create the new clause for SQL Server 2008 */
}
else {
/* do the original else clause stuff */
}
}
{code}
|
|
|
|
|
|
|
|
