Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I am currently watching this video on Entity Framework and about 15 minutes in, the speaker says that table value functions occasionally out perform UDF's.

My question is in what scenario do TVF's out perform UDF's?

share|improve this question
3  
Didn't see the duplicate :/ my bad – Stuart Blackler Dec 28 '11 at 14:29
There are two kinds of TVFs: inline and multi-statement. Typically inline ones perform well. – AlexKuznetsov Jan 3 '12 at 4:19

2 Answers

up vote 6 down vote accepted
  • A scalar UDF with table access acts like a lookup inside a RBAR curso. That is, it executes once per row. And it's black box to the optimier
  • TVF is a macro that expands like a view and is consider part of the outer query

Scalar UDF have uses, but not in this case.

The performance difference can be huge: I've achived x100,000+ improvements by removing idiotic uses of UDFs

share|improve this answer
+1, as for me the query ran 20 sec, after removing, yes - exactly - idiotic UDF, it ran 9 ms – Oleg Dok Dec 28 '11 at 13:03
up vote 2 down vote

When do we need to call the function for every row - than in case of UDF it more looks like cursor loop - because it forces Sql Server to use single-threaded query plan. The TVF has no such restriction

So, the answer is - if the query CAN be parallelized - then TVF outperforms the UDF

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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