Take the 2-minute tour ×
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.

I have a database where i am using inline TVFs (table value functions) instead of views. For example, I might have two tables called [car model] and [car manufacturer] that I'm joining together inside the TVF [fnCarBrands].

These TVFs are then called by other TVFs to do further processing and reporting. So I might take my function [fnCarBrands] and join to the table [Purchase Year] to form a function [fnCarBrandHistory]. And so on for several layers of TVFs.

I could probably get the same functionality using views, since my inline TVFs are really just joins of tables and other TVFs.

How does the performance of inline TVFs written in this way compare with views?

share|improve this question
    
Most likely you would see the same execution plans and the same performance. –  AlexKuznetsov Aug 14 '12 at 16:16
    
that's what i thought, but i was told that the TVF acts like parenthesis would in algebra -- it forces the DB engine to complete that query first before optimizing. Using views, i'm told, allows the optimizer to optimize the whole query as a unit. –  FistOfFury Aug 14 '12 at 16:35
    
Kind of a side note but any idea why TVFs were used instead of views? Just code-monkey vs data-monkey approach to the problem? –  Mark Storey-Smith Aug 14 '12 at 22:13
    
@MarkStorey-Smith yes, the reason is as you say, code-monkey vs data-monkey approach. –  FistOfFury Aug 15 '12 at 15:40
add comment

3 Answers 3

up vote 7 down vote accepted

The query optimizer treats an inline table valued function exactly like a view:

CREATE FUNCTION dbo.InlineUdf(@arg1 int)
RETURNS TABLE
AS
RETURN 
(
    ... your query here ...
);

A multi-statement table-valued function is run more like a stored procedure. They typically have to be executed multiple times, rather than be folded into the main query:

CREATE FUNCTION dbo.MultiStatementUdf (@col1 int)
RETURNS @result TABLE 
(
    id int primary key NOT NULL,
    ... 
)
AS
BEGIN
   DECLARE @var1 int
   set @var1 = 42

   INSERT @result
   SELECT ...
   RETURN
END;
share|improve this answer
    
+1 - Almost word for word what I was going to say! :) –  Mr.Brownstone Aug 14 '12 at 18:17
1  
@Andomar my understanding is multi-statement TVFs are run like a black-box, the engine doesn't know what's in it and can't optimize the query. Do you have any articles you can reference that say views and inline TVFs are equivalent? –  FistOfFury Aug 14 '12 at 21:01
add comment

Of course creating views that call other views is a performance killer too. Do not go down that route. Write the queries you need and don't use either TVFs or views if you want performance. It is the layering that is creating the problem, this is almost always a bad thing to do when querying a database and you can quickly end up hitting the limit of the number of tables you can reference too, especially since you often end up referencing the same tables in different layers. Further, while this seems as if it woudl be easier to maintain, it is not. Try debugging or adding a column due to a new requirement when the layer you need to fix is at the bottom.

share|improve this answer
add comment

You'll need to create similar views to the functions and query each looking at the execution plan to see what's going on with each.

share|improve this answer
    
Exactly. Benchmarking, seeing for yourself - this is the only reliable way to learn. –  AlexKuznetsov Aug 14 '12 at 17:37
    
@mrdenny Is there no way to predict how the optimizer is going to treat each type of query? Surely there are some rules it follows. –  FistOfFury Aug 14 '12 at 21:04
2  
@FistOfFury Yes there are rules, thousands upon thousands of them, complimented with hundreds of developer years of heuristic code. Which is why you'll have to test, or ask Microsoft for the optimiser source code. –  Mark Storey-Smith Aug 14 '12 at 22:08
add comment

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.