March 27, 2008 at 4:57 am
Hi all,
I am confused in choosing between a View and a table-valued UDF and looking for your suggestions. Here is what I want to do.
Select
Column1, Column2, .. ColumnN
From
Table1
Inner Join Table2 on SomeColumns
Inner Join Table3 on SomeColumns
..
Inner Join TableN on SomeColumns
Where
Condition1 ..... Condition N
Of course, I will be consuming this view/UDF in my queries later.
Please note that I have no specific parameters for this query, nor have I any security requirements. I just want to know which one gives better performance. Ideas ????
Thanks,
Mehroz
Syed Mehroz Alam
MyBlog | MyArticles
March 28, 2008 at 5:02 am
Use functions/procedures only if your goal can't be done with view.
If it can't be done with view, you can use:
-stored procedure that returns resultset, if it's passed to client
-function returning table only if the resultset is relatively small and you want to join it to something else or use the resultset in triggers/procedures.
March 28, 2008 at 5:21 am
Hi Robert,
Like SQL server pre-compiles stored prodecures, does it also pre-compiles UDFs or views.
Regards,
Mehroz.
Syed Mehroz Alam
MyBlog | MyArticles
March 28, 2008 at 6:18 am
Of course, however, view plan is combined into query plan.
March 28, 2008 at 9:41 am
IMHO, views are highly preferrable. As a previous poster noted: only use UDF's if it can't be done with a view.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 28, 2008 at 9:57 am
In both cases, the queries are validated at run time. However, view offers best choice if the resultant query is possible with few joins to keep things simple.
Use UDFs only when you cannot make a single query possible for the whole set of tables. IN that case you may conveniently put the intermediate data sets into temporary tables and finally get the actual resultset out of it.
I did not find any special performance benefit in using either of these.
Datts
March 28, 2008 at 10:15 am
Just noticed my posts were too bare bones.
So, views are preferable for several reasons:
- they're highly portable
- they're simpler
- server can better optimize query based on view as view is just an encapsulation of a more complex query.
An UDF returns memory table, not a cursor, so if resultset is huge, it consumes a lot of memory, unless you use Inline Table-valued Function.
March 28, 2008 at 12:06 pm
Syed Mehroz Alam (3/28/2008)
Hi Robert,Like SQL server pre-compiles stored prodecures, does it also pre-compiles UDFs or views.
Regards,
Mehroz.
Actually - no. Not for views.
In the case of an un-indexed view, the view is never compiled on its own. The text of the VIEW gets inserted into the outer query calling the view, and the OUTER query is then compiled.
In the case of an INDEXED view, the view is "materialized" (meaning - the values are turned into a hidden table) and then indexed. Again - no compiling.
---------------------
As to the initial question: table-valued-functions return table variables, which are dodgy for performance (since they don't use statistics, etc...). Because of that, the outer query as to "guess" how many rows it has, and will often make bad query execution choices because of that. Assuming the view is written well, and the underlying query performs well, the view ought to outperform the UDF.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 28, 2008 at 12:18 pm
Matt Miller (3/28/2008)
In the case of an un-indexed view, the view is never compiled on its own. The text of the VIEW gets inserted into the outer query calling the view, and the OUTER query is then compiled.
I don't think so. A view needs to be recompiled if used tables/views change, otherwise you may get weird results/errors. If the view text was inserted into outer query, this wouldn't be necessary.
Compiled view stores plan and column infos, which is combined with outer query plan.
March 28, 2008 at 12:48 pm
Robert (3/28/2008)
Matt Miller (3/28/2008)
In the case of an un-indexed view, the view is never compiled on its own. The text of the VIEW gets inserted into the outer query calling the view, and the OUTER query is then compiled.
I don't think so. A view needs to be recompiled if used tables/views change, otherwise you may get weird results/errors. If the view text was inserted into outer query, this wouldn't be necessary.
Compiled view stores plan and column infos, which is combined with outer query plan.
This may help a bit:
http://msdn2.microsoft.com/en-us/library/ms190237.aspx
Per the article being quoted:
When an SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.
Or from this one on designing indexed queries:
http://msdn2.microsoft.com/en-us/library/ms187864.aspx
Every time a query references a standard view, SQL Server 2005 substitutes the definition of the view into the query internally until a modified query is formed that only references base tables. It then runs the resulting query as usual.
Now here's the blurb on INDEXED views:
If such views are frequently referenced in queries, you can improve performance by creating a unique clustered index on the view. When a unique clustered index is created on a view, the result set is stored in the database just like a table with a clustered index is stored.
Meaning - yes, it's parsed to make sure that the objects exist, etc...but no optimization, no query execution plan is kept, etc...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 28, 2008 at 6:01 pm
That's what they say, but is not necessarily true.
Try this:
create view dbo.vw_test1
as select 1 as A, 234234.234234 as B
go
create view dbo.vw_test2
as select * from dbo.vw_test1
go
select * from dbo.vw_test1
go
select * from dbo.vw_test2
go
alter view dbo.vw_test1
as select 123 as C,'sdasd' as A, 12344.23423 as B
go
--#3 incorrect result
select * from dbo.vw_test2
go
alter view dbo.vw_test2
as select * from dbo.vw_test1
go
--#4 correct result
select * from dbo.vw_test2
go
alter view dbo.vw_test1
as select 'sdasd' as A, 12344.23423 as B
go
--error
select * from dbo.vw_test2
go
drop view dbo.vw_test1
go
drop view dbo.vw_test2
go
This case is simple. You get a lot weirder results, when you have 3 or more level views (ie vw_test3 using vw_test2, vw_test4 using vw_test3, using some joins).
Sometimes #3 case raises "conversion error" instead of returning incorrect result.
If view text was reparsed in outer query, you'd get #4 without rebuilding vw_test2.
As said, compiled view stores column infos and index/fk info.
Drop a fk used to join tables in a view, use the view in a query that would choose FK and you might get an error. These facts are based on experience, not MS documentation.
March 28, 2008 at 8:05 pm
Robert:
All this demonstrates is that the views are binding their output columns when they are saved. This should not be unexpected since, as virtual tables, they have to have the the DML attributes of a table, such as a column set and column defintions.
This does not, however, have anything to do with compiling or saving query plans.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 28, 2008 at 10:51 pm
Robert (3/28/2008)
Just noticed my posts were too bare bones.So, views are preferable for several reasons:
- they're highly portable
- they're simpler
- server can better optimize query based on view as view is just an encapsulation of a more complex query.
An UDF returns memory table, not a cursor, so if resultset is huge, it consumes a lot of memory, unless you use Inline Table-valued Function.
Not quite... A table valued (table variable) UDF returns a memory table just like a temp table does... if the server decides it's getting too big, it jumps to TempDB... just like a temp table does. Please see the following URL... Q3/A3 and Q4/A4 are really good/important pieces of info...
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
--Jeff Moden
Change is inevitable... Change for the better is not.
March 29, 2008 at 3:03 am
like it says User Defined Function is supposed to perform some kind of
functionality (e.g. calculation, regex, ...) for you.
Most of the time based on parameters (row-level !).
Keep it simple and use views wherever you can.
Many times UDF fullfil or contain hidden joins and those are a real pain in
the *** to detect and get rid of.
If you use views, they are actualy mentioned in your join statement, therefor way better to tune/optimize.
ps: and many times respecting KISS principle will pay back in the long run.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 30, 2008 at 1:58 am
Jeff Moden (3/28/2008)
Not quite... A table valued (table variable) UDF returns a memory table just like a temp table does... if the server decides it's getting too big, it jumps to TempDB... just like a temp table does. Please see the following URL... Q3/A3 and Q4/A4 are really good/important pieces of info...http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
Of course, I didn't state it's a memory only table.
If you have a billion row table and a useless statement like this:
select A1.* from atable A1,atable A2
with plain query or view it works just fine, it creates a cursor which reads data as you scroll it. What happens if you do the same in an UDF? :hehe:
With inline table it's the same.
With multi-statement table-valued function... you won't want to wait until it returns.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply