CODEWITHSUNDEEP
sqlsql-serverperformancefunctionjoin
MilkTea027
MilkTea027

Reputation: 301

Joining function taking longer than joining temp table with function data

So I have this scenario in my function where FooIds (function only containing ids) takes a while when joined than inserting it in a temp table:

SELECT TOP 5000 foo1.a, foo2.b, foo1.c
FROM fooA foo1 WITH (NOLOCK)
INNER JOIN fooB foo2 WITH (NOLOCK) ON (foo1.someId1 = foo2.someId1)
INNER JOIN fooIds(@user_id) i ON (i.id = foo1.someId2)
WHERE foo1.someStuff > @userInput
ORDER BY foo1.someId1 ASC
OPTION (RECOMPILE)
RETURN

This takes over an hour!! However the following code takes 2 seconds:

DECLARE @ids TABLE (id int);    
INSERT INTO @ids
SELECT * FROM [FooIds](@user_id)

SELECT TOP 5000 foo1.a, foo2.b, foo1.c
FROM fooA foo1 WITH (NOLOCK)
INNER JOIN fooB foo2 WITH (NOLOCK) ON (foo1.someId1 = foo2.someId1)
INNER JOIN @ids i ON (i.id = foo1.someId2)
WHERE foo1.someStuff > @userInput
ORDER BY foo1.someId1 ASC
OPTION (RECOMPILE)
RETURN

I didn't want to create the extra declared table however this hack seems to be faster than the previous query. Foo1 contains over billions of records, and FooIds over a few thousand.

Upvotes: 0

Views: 322

Answers (1)

dean
dean

Reputation: 10098

As a general rule, avoid using multi-statement table function (and scalar functions with data access) in performance critical situations. Try inlining the code within the outer query instead.

Upvotes: 1

Related Questions