The statement should work fine with say 14K rows in temp@inmemorystorage.
An efficient and better way is probably to switch join strategy using the join_set hint (see Invantive UniversalSQL Grammar 23.0). join_set is used by default when the left-hand side has few entries (such as 5000) to be joined upon.
A join_set uses an IN for the respective platform instead of a full join, and is typically used for instance when joining a last of open sales orders with it’s associated lines. It can be hundred times faster when there are many sales orders, but only 1% is open.
An example:
select /*+ join_set(t1, id, 20000) */ *
from temp@inmemorystorage t1
join transactionlines@sqlserver t1
on t2.id = t1.id
The documentation on Invantive UniversalSQL grammar describes the functionality of join_set.
Some other use cases are available at:
- Itgenase288: The list of values with an IN for GIS_WAARDE must be less than or equal to 1.000 on OracleManaged (Dutch)
- Itgenatr005: Invalid query syntax. Queries are limited to 500 or fewer 'OR' conditions - 2 van forums (Dutch)
Please check in advance that both id columns have the same datatype. Otherwise an implicit datatype conversion will occur, triggering a very expensive join.