sugi wrote:

> > SPs are precompiled and Views are prepared each time you use them.
> > I use views when I know that a certain query will be used multiple
> > times in my application and returns a subset of the data.
> Thank you for the clarification. This is new information for me.
>
> > Views can
> > also be used to limit access rights to tables by assigning the user
> > rights to the view instead of the table.
> Yes, I read about this in an article by Ann Harrison on the ibphoenix
> site recently. Very interesting, but i haven't tried implementing
> this in a production database. I've been doing some reading on Database
> Debunkings site (by Date, Pascal, et al)... wonders whether this might
> be
> a solution for complete 'data independence'...:)
>
> > I use SPs for more complex
> > operations, i.e. statistical data compiled from the financial data
> > from the whole database or to concatenate more than one varchar field
> > together, so one null field would return a null if concatenated with a
> > non-null field.
> OK.
> To be more specific, I needed to provide a server-side construct to
> calculate some data (summary aggregates) for a table. The operation
> is fairly simple, (can be done as a view or an sp), but I'm concerned
> about performance issues when the db got bigger.
>
> I had several options when I started :
> 1. Computed Field.
> 2. SP (returns single value, CALC_ONE in my previous post)
> 3. SP (returns a set, CALC_TWO in my previous post)
> 4. VIEW
>
> #1 is a bit less attractive since I got the impression that the
> calculation will be done every time someone access the table.
> #2 and #3 is equally feasible, but the possibility of slow joins
> from the results are bothering me. As you can see, sooner or
> later I would need to join the result set from the SP to the
> original table. Putting every column that I need into the SP
> seems to be the 'wrong' thing to do.
> #4 is my personal favorite, before I ran into the knowledgeBase
> article that says that I should use SP instead...:)

What I would do is write the one procedure to get the value you need.
Then write another procedure that calls the first procedure that
returns the value you need, plus gets all the other columns that you
need. This way you avoid having to join your SP to tables.

Todder


Todd Brasseur, A.M.A.A.
COMPASS Municipal Services Inc.