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} |
|
