Re: View vs Stored Proc Performance

[Jason Tesser <jasontesser@xxxxxxxxx>]

OK so in my case I have a Person, Email, Phone and Address table.  I want to return the Person and an Array of the others. so my return type would be something like Person, Email[], Phone[], Address[]  

When passed a personId.  

Are you saying this is better in a view.  Create a view that can return that as oppessed to 1. defining a type for a function to return or 2. a function that returns 4 out parameters (Person, Address[] ,....)

Thanks

On Fri, Sep 11, 2009 at 1:37 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:

On Fri, Sep 11, 2009 at 11:46 AM, Jason Tesser <jasontesser@xxxxxxxxx> wrote:
> Is it faster to use a Stored Proc that returns a Type or has Out Parameters
> then a View?  Views are easier to maintain I feel.  I remember testing this
> around 8.0 days and the view seemed slower with a lot of data.

for the most part, a view can be faster and would rarely be slower.
Views are like C macros for you query...they are expanded first and
then planned.  Functions (except for very simple ones) are black boxes
to the planner and can materially hurt query performance in common
cases.  The only case where a function would win is when dealing with
conner case planner issues (by forcing a nestloop for example).

merlin