Posted by Tony Rogerson on 02/04/06 20:15

> 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@ca.ibm.com> wrote in message
news:44k725F2ic39U1@individual.net...
> 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]