|
|
Tested on Oracle 11g and as I expected, I could no reproduce the stalling behavior. An interesting tidbit is that Oracle generates a SQL statement like the following:
{code}
WHERE (ITEM_ID,PLANT_ID) IN ((?,?),(?,?),(?,?),...)
{code}
which the equivalent SQL Server's clause is:
{code}
WHERE ((ITEM_ID=? AND PLANT_ID=?) OR (ITEM_ID=? AND PLANT_ID=?) OR ... (ITEM_ID=? AND PLANT_ID=?))
{code}
I am curious if the following might be a more efficient SQL Server equivalent on 2008 and up:
{code}
SELECT *
FROM EntityClass AS T
WHERE EXISTS (
SELECT *
FROM (VALUES (?,?), (?,?), (?,?), (?,?)) AS V(ITEM_ID,PLANT_ID)
WHERE T.ITEM_ID = V.ITEM_ID
AND T.PLANT_ID = V.PLANT_ID
)
{code}
This avoids the 'OR' operator and from what I have understood performs better but have yet to test it. -I'm trying to see if there is any logical way to create such an EXISTS expression but so far I haven't found one yet.-
So I created a query above using a simple {{SQLQuery}} to at least test the timing compared to the normal generated SQL and the performance difference was amazing. You're talking less than 100ms response time versus 6-7 seconds where the above doesn't have any stalls in streaming the results to the client.
Is there anyway that the Dialect for SQL Server can be inspected and should it be 2008 or higher, the above type of SQL gets generated rather than using the {{Disjunction}} logic which only seems to perform decently for a small {{maxResults}} value.
|
|
|
|
|
|
|
|
