Joining on table-valued function slows down query

Question

Orders is 350k rows. Users is 20k rows. Reflinks is 2k rows. @p0 is 40k ids. SplitIds(@p0) splits @p0 and returns TABLE(Id INT NOT NULL PRIMARY KEY). Looks like it executes immediately.

First SELECT executes immediately and returns 7.5k rows. Its execution plan completely based on 'Hash Match' blocks.

Second SELECT executes 120s and returns same 7.5k rows. Its execution plane same, but based on Nested Loop blocks.

Is it possible somehow to speed up the second query? My task is to explicitly specify the list of input orders, that's why I created SplitIds. If you know how to do it without SplitIds - your ideas will be appreciated too.

DECLARE @p0 varbinary(max);
SET @p0 = 0x.........; // LOT OF BYTES 

SELECT Orders.Id, Reflinks.Rate1
FROM Orders
INNER JOIN Users ON Orders.UserId = Users.Id
INNER JOIN Reflinks ON Users.ReflinkId = Reflinks.Id

SELECT Orders.Id, Reflinks.Rate1
FROM SplitIds(@p0) AS t0
INNER JOIN Orders ON t0.Id = Orders.Id
INNER JOIN Users ON Orders.UserId = Users.Id
INNER JOIN Reflinks ON Users.ReflinkId = Reflinks.Id

Answer 1

If you are willing to use temporary tables, you could store the result from SplitIds in a properly indexed temporary table, and perform the second query using it instead. That will certainly speed up things, because SQL Server will be able to create a better execution plan for the query.

CREATE TABLE #temp(Id INT NOT NULL PRIMARY KEY)

INSERT INTO #temp(Id) SELECT Id FROM SplitIds(@p0)

SELECT Orders.Id, Reflinks.Rate1
FROM #temp AS t0
INNER JOIN Orders ON t0.Id = Orders.Id
INNER JOIN Users ON Orders.UserId = Users.Id
INNER JOIN Reflinks ON Users.ReflinkId = Reflinks.Id