38

I have been doing a lot of reading up on execution plans and the problems of dynamic parameters in stored procedures. I know the suggested solutions for this.

My question, though, is everything I have read indicated that SQL Server caches the execution plan for stored procedures. No mention is made of Table-value functions. I assume it does so for Views (out of interest).

Does it recompile each time a Table-value function is called?

When is it best to use a Table-value function as opposed to a stored procedure?

IamIC
  • 17,747
  • 20
  • 91
  • 154
  • possible duplicate of [Functions vs Stored Procedures](http://stackoverflow.com/questions/178128/functions-vs-stored-procedures) – Gert Arnold Feb 23 '14 at 12:04
  • This question was asked and answered 3 years ago. What's the purpose of closing it know even if it is a duplicate as alleged? – IamIC Feb 24 '14 at 10:19

2 Answers2

36

An inline table valued function (TVF) is like a macro: it's expanded into the outer query. It has no plan as such: the calling SQL has a plan.

A multi-statement TVF has a plan (will find a reference).

TVFs are useful where you want to vary the SELECT list for a parameterised input. Inline TVFs are expanded and the outer select/where will be considered by the optimiser. For multi-statement TVFs optimisation is not really possible because it must run to completion, then filter.

Personally, I'd use a stored proc over a multi-statement TVF. They are more flexible (eg hints, can change state, SET NOCOUNT ON, SET XACTABORT etc).

I have no objection to inline TVFs but don't tend to use them for client facing code because of the inability to use SET and change state.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Wow, so the bottom line is only stored procedures cache execution plans, and are therefore the most efficient method of querying data, beating even views. – IamIC Nov 23 '10 at 12:43
  • 1
    @IanC: views also cache (like inline TVFs). Stored procs are just far more flexible (eg if I want SET NOCOUNT ON, SET XACTABORT + TRY/CATCH) – gbn Nov 23 '10 at 12:49
  • I'm using multi-statement TVFs for varied parameterised input, with the "OPTION (OPTIMIZE FOR (@JobID UNKNOWN....)) inclusion. Is this optimum? – IamIC Nov 23 '10 at 13:34
  • 1
    @IanC: The OPTION hint may be ignored. Multi-statement TVFs are notorious black boxes: I'm not sure it will propogate into the TVF. Links: [One](http://stackoverflow.com/questions/510743/why-is-udf-so-much-slower-than-subquery/510754#510754), [Two](http://stackoverflow.com/questions/311026/does-query-plan-optimizer-works-well-with-joined-filtered-table-valued-functions/311163#311163) – gbn Nov 23 '10 at 13:38
  • I assume stored procs are also "black boxes". Given what you've said and what I've read, it seems the only efficient way to generate a multi-row table result set that can participate in an SQL statement is to use an inline table function. That can be tricky. – IamIC Nov 24 '10 at 04:26
  • 4
    What we mean by black boxes is this: your tvf returns 20 columns. You do `SELECT col1, col2 from tvf WHERE foo = bar`. An inline TVF (or a stored proc that has only col1 and col2) will work out the plan for col1, col2 with the where. A multi TVF has to run for all 20 columns, spool results, then filter, then restrict to 2 columns. – gbn Nov 24 '10 at 06:49
  • @gbn I get what you're saying about *columns*. The multi tvf cannot see the outer select, so returns everything (in this example), and this result is then filtered. I am asking about *rows*. I know if we say "SELECT id, dbo.get_max" where get_max is a multi TVF, it will be called for every row. That makes sense. What I'm saying is it seems to me that despite this behavior, it would be called only once for something like the following: "select x from dbo.get_dataset" AND for: "select x, t.y from table inner join get_dataset t on x.id = t.id" Am I correct? – IamIC Nov 24 '10 at 07:30
  • @IanC: yes. Called once in the FROM clause. – gbn Nov 24 '10 at 07:31
  • @gbn Thank you for your help! I have one more question, if you don't mind. Inline TVFs seem to be the performance winner where the desired result is a table. We have looked at query plan caching and optional parameters. An inline TVF does cache plans, but cannot take the "OPTION OPTIMIZE FOR ? UNKNOWN" statement. Does this mean inline TVFs will suffer the same fate as stored procs where the 1st execution causes the caching for *that* parameter value set, and all subsequent calls use this original plan? Or does SQL Server only cache a generic plan, omitting the params? – IamIC Nov 24 '10 at 07:39
  • And do scalar functions cache execution plans? – IamIC Nov 24 '10 at 07:47
  • 1
    @IanC: The "OPTION OPTIMIZE FOR ? UNKNOWN" would go in the outer query that uses in the inline TVF (This applies to most hints BTW). All plans are equal too whether stored proc or ad-hoc batch or a view using an inline TVFs. Scalar: if the scalar function does table access then it's just as bad as a multi TVF. It probably has a plan but I can't remember now :-) – gbn Nov 24 '10 at 10:20
  • I think actually everything caches a query plan where possible. It isn't restricted to certain classes of DB access. The key is to remember the hint, in the right place, and try and avoid multi-statement table functions at all costs. :) Even in the article on it http://msdn.microsoft.com/en-us/library/ee343986(SQL.100).aspx, MS don't quite spell it out clearly. – IamIC Nov 24 '10 at 13:20
  • But you can't return a table with a stored procedure, right? Don't stored procedures always return an integer? If your stored procedure has a select statement in it, can you use the result of that select statement when executing the procedure? – Kyle Delaney Jan 05 '17 at 23:41
1

I haven't verified this, but I take for granted that the execution plan for functions are also cached. I can't see a reason why that would not be possible.

The execution plan for views are however not cached. The query in the view will be part of the query that uses the view, so the execution plan can be cached for the query that uses the view, but not for the view itself.

The use of functions versus stored procedured depends on what result you need from it. A table-valued function can return a single result, while a stored procedure can return one result, many results, or no result at all.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • 1
    One thing I noticed is I can't add "OPTIMIZE FOR UNKNOWN" to functions, which makes me think it is null and void. – IamIC Nov 23 '10 at 10:22