Posted by Serge Rielau on 02/04/06 19:37

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

[Back to original message]