Calling T-SQL table-valued function multiple times faster than calling once?

Question

I'm confused by something. I'm using T-SQL and trying to optimize code that looks like this:

SELECT * FROM MyTable mt
LEFT JOIN
  (SELECT * FROM Table1 t1
   LEFT JOIN api.tableValuedFunc(@someArgs) tvf
   on tvf.Key = t1.Key) firstJoin
on mt.Key = firstJoin.key
LEFT JOIN
  (SELECT * FROM Table2 t2
   LEFT JOIN api.tableValuedFunc(@someArgs) tvf
   on tvf.Key = t2.Key) secondJoin
on mt.Key = secondJoin.key

by doing this:

declare @tvfResult TABLE ( ... some columns ... )
insert into @tvfResults SELECT * FROM api.tableValuedFunc(@someArgs);

SELECT * FROM MyTable mt
LEFT JOIN
  (SELECT * FROM Table1 t1
   LEFT JOIN @tvfResults tvf
   on tvf.Key = t1.Key) firstJoin
on mt.Key = firstJoin.key
LEFT JOIN
  (SELECT * FROM Table2 t2
   LEFT JOIN @tvfResults tvf
   on tvf.Key = t2.Key) secondJoin
on mt.Key = secondJoin.key

This doubles the time it takes to execute! Why is this? In the second example, I (naively) assume I'm executing the function half as often. Is there some kind of SQL wizardry happening behind the scenes that I'm ruining?

Looking around, this seems related to stackoverflow.com/questions/4109152/… But the prescription in this case is to use a temp table, which is what I'm doing. — Peter Dowdy
– Peter Dowdy, Commented Jun 2, 2016 at 22:59
OK, apparently that's not a temp table but a table variable, which is a ton slower. This all needs to live in a function, though, so I can't use temp tables. Is option #1 as fast as I'm going to get? — Peter Dowdy
– Peter Dowdy, Commented Jun 2, 2016 at 23:06
Please confirm whether or not you are actually using "SELECT * " in your measured queries, or are merely using it as shorthand in the question. Note that the mere use of "SELECT * " can be a sever performance hit due to the unavailability of any covering indices. Next, please attach an ACTUAL QUERY PLAN from SSMS for analysis - How can we possibly identify what is happening without either a query plan or the full DDL for all tables/views/etc involved? — Pieter Geerkens
– Pieter Geerkens, Commented Jun 3, 2016 at 0:58

ydoow · Accepted Answer · 2016-06-02 23:31:22Z

1

Instead of creating a table variable, create a local temporary table may do better performance if it's outputting large number of rows.

create table #tvfResult  ( ... some columns ... )

insert into #tvfResults SELECT * FROM api.tableValuedFunc(@someArgs);

SELECT * FROM MyTable mt
LEFT JOIN
  (SELECT * FROM Table1 t1
   LEFT JOIN #tvfResults tvf
   on tvf.Key = t1.Key) firstJoin
on mt.Key = firstJoin.key
LEFT JOIN
  (SELECT * FROM Table2 t2
   LEFT JOIN #tvfResults tvf
   on tvf.Key = t2.Key) secondJoin
on mt.Key = secondJoin.key

Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server.

Drop the temporary table explicitly if you are re-running the query with the same session.

drop table #tvfResult

answered Jun 2, 2016 at 23:31

ydoow

3,0064 gold badges27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Peter Dowdy Over a year ago

I want to do that, but all of this needs to live inside of a function itself, so a temp table is off-limits, unfortunately.

ydoow Over a year ago

you can create a local temporary table inside a function, isn't it?

Peter Dowdy Over a year ago

I thought so, but SQL server throws an error: Cannot access temporary tables from within a function.

IVNSTN Over a year ago

tvf inside of tvf - starting smell not good. Try rethinking your approach and main idea of the way you're attempting to implement the solution.

Peter Dowdy Over a year ago

I inherited this code and am currently evaluating a re-factor.

Pieter Geerkens · Accepted Answer · 2016-06-03 01:10:27Z

Note that, unless you have omitted several critical factors from your question, your first query reduces to merely:

SELECT * FROM MyTable mt
LEFT JOIN Table1 t1 on mt.Key = t1.key
LEFT JOIN Table2 t2 on mt.Key = t2.key
LEFT JOIN api.tableValuedFunc(@someArgs) tvf1 on tvf1.Key = t1.key
LEFT JOIN api.tableValuedFunc(@someArgs) tvf2 on tvf2.Key = t2.key

Now, the analysis reduces to what your actual column selection is in the SELECT clause - please supply those details as it is impossible to analyze your query without actually knowing what the query is - in excruciating detail - because performance problems are invariably in the details.

I elided a fair amount due to the function in question being over 600 lines long. Here's the execution plan for it as-is: pastebin.com/7Y4axtKB And with my mis-optimization: pastebin.com/NZuZHkY1

Collectives™ on Stack Overflow

Calling T-SQL table-valued function multiple times faster than calling once?

2 Answers 2

5 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Linked

Related