[hibernate-issues] [JIRA] (HHH-8370) Support RowValue constructor syntax in in lists for SQL Server 2008

Christian Beikov ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *updated* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiOWI0OGJmZTQ1... ) / Improvement ( https://hibernate.atlassian.net/browse/HHH-8370?atlOrigin=eyJpIjoiOWI0OGJ... ) HHH-8370 ( https://hibernate.atlassian.net/browse/HHH-8370?atlOrigin=eyJpIjoiOWI0OGJ... ) Support RowValue constructor syntax in in lists for SQL Server 2008 ( https://hibernate.atlassian.net/browse/HHH-8370?atlOrigin=eyJpIjoiOWI0OGJ... ) Change By: Christian Beikov ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) As discussed in [ HSEARCH-1367 ] : it seems that when loading a list of entities by primary key is slow on SQL Server 2008 when the key is composite. It was reported that we generate a more efficient SQL statement for Oracle, and that this SQL Server version actually would support this same syntax and this would fix the performance problem. See linked issue for all details. In short, the aim of this enhancement request is that {noformat}WHERE (FIELD1,FIELD2) IN ((?,?),(?,?),...){noformat} which doesn’t work on SQL Server is rendered with EXISTS and VALUES like this {noformat}SELECT * FROM EntityTable T WHERE EXISTS ( SELECT * FROM (VALUES (?,?), (?,?), (?,?)) AS V(FIELD1, FIELD2) WHERE T.FIELD1 = V.FIELD1 AND T.FIELD2 = V.FIELD2){noformat} This is apparently faster on SQL Server than the regular emulation: {noformat}WHERE ((T.FIELD1=? AND T.FIELD2=?) OR (T.FIELD1=? AND T.FIELD2=?) OR ... (T.FIELD1=? AND T.FIELD2=?)){noformat} ( https://hibernate.atlassian.net/browse/HHH-8370#add-comment?atlOrigin=eyJ... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-8370#add-comment?atlOrigin=eyJ... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100209- sha1:13f5f09 )