Need Project Advice, Table-Valued Functions Versus Views

Question

Need Project Advice, Table-Valued Functions Versus Views

HoneyChi

Ten Centuries

Points: 1084
More actions
February 4, 2005 at 12:44 pm

#66491

I have an upcoming project to optimize a SQL database that contains a multitude of views, most of which are many levels deep. A developer who is no longer with the organization created this view-upon-a-view-upon-a-view design. I am not comfortable with the current design, which I believe is not robust and is too easily broken by the slightest change to a table, not to mention difficult to maintain. One of my reference manuals states, "If you use a view to read data only, you will benefit from converting this view into a table-valued function because it will be optimized and compiled on the first execution, providing performance gains over a view." (Taken from "SQL Server 2000 Programming by Example, Fernando G. Guerrero and and Carlos Eduardo Rojas"). Anyone have thoughts on this, or actual experience? Any other ideas?
TIA, Linda

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

michanne SSCrazy Points: 2329 More actions · Answer 1

My exp is often that the functions are slightly slower but usually not enough to supercede the benefits over views...

I have found functions to be a really good choice when you can include a variable(s). Both the view and the function pull in all the data but with a function you can restrict the data with a variable in the function rather than just with the where clause.

Also the functions allow you to nest table variables so you do not need to have that additional interface. Then you would have one long function instead of bunches.

Often - i ended using some combination of functions and perm tables to aggregate the data - the logic being that if im gonna pull the data over and over and optimization is an issue(and it usually is) - why not have a perm source? Sometimes i datestamp and expire it after a set period of time.

HoneyChi Ten Centuries Points: 1084 More actions · Answer 2

Thank you, michanne. Since my initial post I've been doing some benchmarking and unfortunately the view is still much faster than the identical rowset acheived using a function. Oh well, it was a thought....

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

Couple of comments on this ..

If the concern is underlying table changes impacting views, how are functions going to be any less prone to breaking if, say, a column name changes in a table the function is referencing ?

Secondly, for a large enough result set, the @Table variable being built by a function may actually end up written to disk before being returned from the function, if not enough memory is available for page buffer cache. @Table variables are often thought of as "in-memory" temp tables, but they can cost disk IO depending on size and cache use.

HoneyChi Ten Centuries Points: 1084 More actions · Answer 4

First point, quite right. It's more a concern of so many "layers" of different views being more difficult to maintain when structure changes occur, as opposed to maintaining a function or a single-level view. Second point, very good and useful information. Thanks for the comments, PW.... Linda