I have taken over a legacy application that has had many performance issues. One I found is on a table valued function.

The TVF runs fine as a basic SELECT. However, it is incredibly slow when it has a WHERE clause on it.

I would have thought that the WHERE filter is applied after the TVF query has run?

I don't understand how the WHERE clause can have such an impact on performance.

The query is:

select * from tvf(@id, @name) where id = @id
| improve this question | | | | |
  • 3
    Is it a multi-statement table value function or an inline table value function? – Daniel Hutmacher Feb 27 '16 at 10:32
  • I presume that id is indexed? – Vérace Feb 27 '16 at 10:33
  • 7
    The answer is in the query plan. If you could provide the xml version of the plans you get with the where clause and without the where clause perhaps someone could tell you what is going on. – Mikael Eriksson Feb 27 '16 at 10:48
  • 4
    Also, if the TVF takes @id as a parameter, what is the point of the outer filter? – Aaron Bertrand Feb 27 '16 at 12:06
  • The TVF might do something like "and its children", so that the filter reduces the results. – Rob Farley Feb 27 '16 at 22:15

This would be a "bad plan" scenario. Essentially, that when expanded out for that particular set of values, your query is being executed in a way that takes a long time - such as performing many lookups instead of using a wider index.

Expand out the plan for each, and it should become clear. And see if there are certain values that cause your pain, and certain ones that don't.

As a workaround, you could dump the results to a temporary table and then query that with the filter. But still look at the plans and learn what's going on.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.