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 https://stackoverflow.com/questions/4109152/table-valued-function-killing-my-query-performance But the prescription in this case is to use a temp table, which is what I'm doing. — Peter Dowdy, Jun 02 '16 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, Jun 02 '16 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, Jun 03 '16 at 00:58

score 1 · Answer 1 · answered Jun 02 '16 at 23:31

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 02 '16 at 23:31

ydoow

2,969
4
24
40

1

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. – Peter Dowdy Jun 02 '16 at 23:34
you can create a local temporary table inside a function, isn't it? – ydoow Jun 02 '16 at 23:35
1

I thought so, but SQL server throws an error: Cannot access temporary tables from within a function. – Peter Dowdy Jun 02 '16 at 23:41
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. – Ivan Starostin Jun 03 '16 at 06:57
1

I inherited this code and am currently evaluating a re-factor. – Peter Dowdy Jun 03 '16 at 16:57

score 0 · Answer 2 · answered Jun 03 '16 at 01:10

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: http://pastebin.com/7Y4axtKB And with my mis-optimization: http://pastebin.com/NZuZHkY1 — Peter Dowdy, Jun 03 '16 at 17:50

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

2 Answers2