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