Re: Views vs Stored Procedures, whats the difference?

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: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)
  • RE: slow server response
    ... If you come to SQL from a procedural background you're use to working record by record but in SQL you work in sets. ... Most uses of cursors and temp tables can be reprogrammed to use set therory. ... "saif" wrote: ... > I have created a stored procedure which reads results from a table using ...
    (microsoft.public.sqlserver.programming)
  • Re: disable/enable all SQL Agent jobs in a non cursor approach
    ... always heard dreaded things about cursors and was just trying ... because you should be calling a stored procedure for ... > What is wrong with a cursor approach for this type of one-off, admin task? ... can one provide the SQL for it ...
    (microsoft.public.sqlserver.programming)
  • 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)
  • Re: Issue with retrieving large data over web using Stored Procedu
    ... how do I go about analyzing a stored procedure with selecting ... Is there any tool in the SQL Profiler that analyze each Trace? ... "Active Server Pages error 'ASP 0113' ... This email account is my spam trap ...
    (microsoft.public.inetserver.asp.db)