Board index » delphi » Stored Procedure vs Views

Stored Procedure vs Views

Can anybody please explain to me if it is better to use a View on SQL server
or is it better to use a Stored Procedure with a normal SELECT SQL statement
to get a recordset to use on a report.

Thanx

 

Re:Stored Procedure vs Views


Quote
> Can anybody please explain to me if it is better to use a View on SQL
server
> or is it better to use a Stored Procedure with a normal SELECT SQL
statement
> to get a recordset to use on a report.

View is calculated dynamically, as data in underlying tables change. So, if
you absolutely don't need a view, you don't create it because it eats server
performance.
Because normal reports are needed periodically, you don't need resulting set
to be available "all the time" in view.
So, in normal reports, you don't use view.

Re:Stored Procedure vs Views


Buch,

Of course your milage may vary, but I disagree.  Using views, I've seen
nothing but performance increases over client initiated selects joining
numerous tables.  Views make end-user reporting a lot simpler.  End users
can simply select one view to do their report versus having to remember
always having to join 15 tables.  Views add a good layer of security if you
want to restrict certain fields (like credit card info) from end users.

To give you an example, the owner of the company wanted a field added to the
customer table for the last time a customer ordered from us (LastOrderDate).
Did I add the field and modify every application, stored procedure, trigger,
to accommodate the new field?  Heck no.  I simply added a sub-query off the
view for his one report (that's right, only one report).

Select FirstName.....
 (Select MAX(InvoiceDateTime) from ..where...) [LastOrderDate]
From Customers ...
Where .....

Stored procedures can give you a large amout of flexibility and security,
but not very many report generators support stored procedures without some
kind of programming from the developer.

We use both stored procedures and views for reporting purposes and I haven't
seen any noticeable performance difference between the two, but again, your
milage may vary.

Good luck,
krf

Quote
Buch <b...@iname.com> wrote in message news:3b0bc352_2@dnews...
> View is calculated dynamically, as data in underlying tables change. So,
if
> you absolutely don't need a view, you don't create it because it eats
server
> performance.
> Because normal reports are needed periodically, you don't need resulting
set
> to be available "all the time" in view.
> So, in normal reports, you don't use view.

Re:Stored Procedure vs Views


Quote
Kevin Frevert wrote:
>  Using views, I've seen
> nothing but performance increases over client initiated selects joining
> numerous tables.

Kevin,

I completely agree.  In Oracle, the DBMS parses and compiles the view and stores
it in compiled format, thus avoiding the equivalent process for a select.  I
expect that SqlServer does the same, or equivalent.

John Pierce

Re:Stored Procedure vs Views


In article <3b0bde53$1_1@dnews>, kfrev...@midwayusa.com says...

Quote
> Buch,

> Of course your milage may vary, but I disagree.  Using views, I've seen
> nothing but performance increases over client initiated selects joining
> numerous tables.  Views make end-user reporting a lot simpler.  End users
> can simply select one view to do their report versus having to remember
> always having to join 15 tables.  Views add a good layer of security if you
> want to restrict certain fields (like credit card info) from end users.

I also disagree, although I suspect you will find very little difference
in performance between using a view and a stored procedure.  IIRC, in
SQLServer a view only caches its sequence tree.  Therefore each time you
call a view, the execution plan has to be compiled.  This sounds like,
and technically is, a performance hit.  But for a report it's not for
several reasons.  First, reports are needed infrequently relative to the
other ways you use the database, so the execution plan that was compiled
for the stored proc is most likely going to be already aged out of
memory the next time you want to run the report.  Second, the data has
probably changed fairly significantly since the last time you ran the
report (otherwise why run it?) so a new execution plan is possibly a
good thing as opposed to using an out of date plan.  And why put the
server through the work of caching that execution plan (by using a
stored proc) since in all likelihood it's not going to still be in the
cache next time you want it?

Plus views offer other advantages to reports such as being able to bind
the view to the schema so that you can't drop tables that the view
depends on or change the table definition if it affects how the view
uses the table.

And finally, views, being logical tables, are almost always easier for
reporting tools as well as users to deal with and understand.

Re:Stored Procedure vs Views


Hi,

Well, You'll use SP if you want something to be computed or to be proofed.
Said: The 10 biggest Earning Ratio for a given "Portefeuille"...
You'll use a view in the case you need an "Special Insight": a View can be
seen as a special Table that manage Datas coming from many sources
(Combination).
Use a TQuery for a View and a TStoredProc for a SP (not in all the case but
generally...;-)

There's no "right" or "wrong" in prefering View or SP.
Each situation may have a different solution. Interbase helps you with
Performance Analysis...

Bye,
Lo?s.

"Christo Botha" <chris...@scsolutions.co.za> schrieb im Newsbeitrag
news:3b0bb641_1@dnews...

Quote
> Can anybody please explain to me if it is better to use a View on SQL
server
> or is it better to use a Stored Procedure with a normal SELECT SQL
statement
> to get a recordset to use on a report.

> Thanx

Other Threads