Re: Views vs Stored Procedures, whats the difference?

One does not exclude the other. You can use procedures as external
interface for the application and still use views within the procedures as
appropriate.

Absolutely, so long as you are using stored procedures as the external
access mechanism then that is a good thing.

I hope you are not suggesting you embed SQL queries into the application?

If you are embedding SQL, just how are you going to stop users from
accessing those views directly through their desktop applications? You can
be in all kinds of mess.

Absolutely not true.
A stored procedure logic will be exactly as fast as the algorithm you
chose when you wrote it. The DBMS can neither optimize nor parallelize
stored procedure logic. It can only optimize teh small pieces of SQL that
you left in.
The SQL inside the view will be as fast as the optimizer can make it
depending on the statistics using 30 years of research. The entire idea
RDBMS and SQL is to separate the WHAT from the HOW. Anytime you use logic
you take the HOW away from the RDBMS and you have exactly one choice to
combine results: Nested loop join (aka nested cursors).

Yes it is true from the optimiser point of view - the execution plan will
remain in cache unless kicked out; if it is kicked out the the procedure
will be recompile, and that means the SQL within it and that is the level at
which views operate, the proc is simply a container for the SQL.

I am not talking about writing cursors, placing optimiser hints on the SQL
either (which you can do with views as well).

These two, asside from an unmeasurable amount of CPU time in plan
compilation will give the same performance...

SELECT ... FROM yourview

Or..

CREATE PROC yourproc
AS
BEGIN
SELECT .... FROM yourview

END

The difference is the second one is modular and gives development and
support benefits far above just calling the view in isolation.

The fast money perhaps, absolutely not the smart money.
Folks like Joe and I spend a lot of time digging companies who fell prey
to this thinking out the ditch.

If you are championing embedded SQL in applications then god help those
companies and all I can say is - catch up with the current thinking around
modular, **secure** and supportable programming methods.

The smart developer considers such things as scalability, maintainability
and supportability, they do not try to apply 80's programming models where
most products didn't have the concept of stored procedures, I remember - I
used to program PL/1, CICS and DB2 on the mainframe that way.

How good is your QA, do you design limits testcases for each and every
combination of IF THEN ELSE logic?

Your QA needs to test all possible routes through your code 'period'

That is more easily accomplished at a unit test level with a small module
aka the stored procedure. Trying to find and execute all those embedded SQL
statements is a really dumb way of going about QA and design and will lead
to bugs and security problems.

Do you believe the harm of a nested cursor is undone by the advantage of
an IF THEN ELSE statement?
I understant that SQL Server supports hints.
Try a couple experiements forcing SQL Server to use different join
implementations (nestedloop, merge, hash, ..) on decent sized tables, then
think about whether you can afford nested loop (i.e. nested cursors).
My company sells hardware, I don't mind if the stuff you produce requires
resources beyond measure :-)

I've no idea what you are getting at there.

With SQL Server you can use IF ELSE to great affect, consider this query...

SELECT ....
FROM table
WHERE mycol1 = COALESCE( @myparm1, mycol1 )
AND mycol2 = COALESCE( @myparm2, mycol2 )
etc...

In Microsoft SQL Server you will get a general plan, now, if only @myparm1
is passed and @myparm2 is NULL it is better to code this...

SELECT ....
FROM table
WHERE mycol1 = @myparm1

In a stored procedure you can put IF ELSE logic to determine the parameters
passed and execute the correct (best) statement accordingly, if you have a
lot of parameters there are things you can do with dynamic SQL within the
stored procedure.

Tony Rogerson, SQL Server MVP
http://sqlserverfaq.com


"Serge Rielau" <srielau@xxxxxxxxxx> wrote in message
news:44k725F2ic39U1@xxxxxxxxxxxxxxxxx
Tony Rogerson wrote:
If you use VIEWS to encapsulate logic and your security just how do you
prevent a user from connecting to the database and writing their own
queries?

Answer: With VIEWS you can't (easily) but with stored procedures they
don't get the opporunity, nor is your schema design (and possible
pitfalls) sent across the wire for all and sundry to see your bad
habits - and exploit them!
One does not exclude the other. You can use procedures as external
interface for the application and still use views within the procedures as
appropriate.

You view will have a different plan each time its compiled anyway - when
values change, statistics distribution etc... it behaves just like a
stored procedure in that respect.
Absolutely not true.
A stored procedure logic will be exactly as fast as the algorithm you
chose when you wrote it. The DBMS can neither optimize nor parallelize
stored procedure logic. It can only optimize teh small pieces of SQL that
you left in.
The SQL inside the view will be as fast as the optimizer can make it
depending on the statistics using 30 years of research. The entire idea
RDBMS and SQL is to separate the WHAT from the HOW. Anytime you use logic
you take the HOW away from the RDBMS and you have exactly one choice to
combine results: Nested loop join (aka nested cursors).

The smart money use stored procedures because:
The fast money perhaps, absolutely not the smart money.
Folks like Joe and I spend a lot of time digging companies who fell prey
to this thinking out the ditch.

1) they neatly encapsulate logic so
a) it can be easily developed in a multi-person team/teams
b) faults can be very easily diagnosed and solved without
having to revert to a nasty application recompile and redistribution
2) they are really great for implementing proper security, your
schema is not exposed to anybody except administrators and app devs.
Both points above are orthoginal to the usage of views.
3) you can code IF ELSE to make more efficient queries instead of
getting general plans and tons of code that needs testing and supporting
How good is your QA, do you design limits testcases for each and every
combination of IF THEN ELSE logic? Do you believe the harm of a nested
cursor is undone by the advantage of an IF THEN ELSE statement?
I understant that SQL Server supports hints.
Try a couple experiements forcing SQL Server to use different join
implementations (nestedloop, merge, hash, ..) on decent sized tables, then
think about whether you can afford nested loop (i.e. nested cursors).
My company sells hardware, I don't mind if the stuff you produce requires
resources beyond measure :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab


.

Relevant Pages

  • Re: Report to display data from sql serv.
    ... IIF (case statements in SQL Server) scenarios etc. ... then you need to create a Stored Procedure in SQL Server and use that as ... at run time supplying the parameters in code by using the Exec command. ...
    (microsoft.public.access.reports)
  • Re: ADO stored proc
    ... to know how to access a SQL Server stored procedure using vbscript ... I've looked at vbscript books and sql server books on ... I don't like this technique since: ...
    (microsoft.public.scripting.vbscript)
  • Re: Stored Procedures vs DTS vs Jobs
    ... A stored procedure is complied code meaning that SQL Server has already ... > execution time, ...
    (microsoft.public.sqlserver.dts)
  • Re: Tracing user & permissions (triggers and stored p)
    ... To solve my problem I chosed to configure the SQL ... > from xp_cmdshell executes under the security context of the SQL Server ... >> sql Trigger executes a Stored Procedure passing it some variables. ...
    (microsoft.public.sqlserver.security)
  • Re: Stored Procedures - Patterns and Practices
    ... >published the reasoning behind its opinions. ... I disagree that the debate in SQL Server related discussion forums ... If the natural key is long or spans too many ... I want to call a stored procedure that adds a customer ...
    (microsoft.public.sqlserver.programming)