Re: Views vs Stored Procedures, whats the difference?

Tony Rogerson wrote:
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.
OK.. perhaps we violently agree? You are hung up on embedded SQL. I'm not debating that point at all.

I hope you are not suggesting you embed SQL queries into the application?
No I'm not. Each their paradigm. I'm just trying to differentiate between two fundamentally different SQL objects.

<snip>

These two, asside from an unmeasurable amount of CPU time in plan compilation will give the same performance...
So the CALL is free? No doubt it's highly optimized, but it won't be free.

SELECT ... FROM yourview

Or..

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

END
You are looking only at one end of the picture.
Let's focus on the scenario where the select is NOT in the application, but it is inside of a nested proecdure.

Let's use the following SQL (never mind if I mess up the dialect.. that's not the point):
CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, C1 INT)
CREATE TABLE C(pk INT NOT NULL PRIMARY KEY,
fk INT FOREIGN KEY T(pk), C2 INT)
CREATE PROCEDURE nestedproc(IN arg)
BEGIN
SELECT c1, c2 FROM P, C WHERE P.pk = C.fk AND P.pk = arg;
END

CREATE VIEW nestedview AS SELECT c1, c2 FROM P, C WHERE P.pk = C.fk;

Now let's consider the following:
"What is the maximum value c2 for a given arg?"
Using the procedure you CALL the procedure with the argument and you then have to process the resultset (with swoem sort of logic unless I missed some T-SQL feature) to find the MAX.
Using the view I simply write:
SELECT MAX(c2) FROM nestedview
WHERE pk = arg

What's the difference? An SQL optimizer can clearly see that there is nothing interesting in P. It will DROP access to table P altogether because first it projects out c1, then it kicks out P using the foreign key relationship to prove that the join is row preserving.
Even better, with the right index (fk, c2 desc) this whole thing will be done in a single I/O.

Using the procedure you have broken this vital link between the consumer of the data and the data access. The DBMS has no clue that c1 will never be consumed because the procedure is written generically for users who want to retrieve c1 and c2.
Now, even if your DBMS were not to do this optimization today. Chances are it will next time you upgrade; the DBMS will show why you paid more for the new release than the old one.

The problem lies NOT in the application. The problem lies in the fact that procedures are being nested and important context is being lost.

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.
You are older than me... anyway being too heavy on procedures is poison for scalability. When I teach customers I teach that both logic and SQL have their place. What I call "trivial procedures" are my prime example to explain what I mean.

Cheers
Serge

PS: Check out table functions (aka parameterized views).
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
.

Relevant Pages

  • Re: SQL Express - Identity specification property - how to change
    ... FOO_ID int identity, ... Tony Rogerson, SQL Server MVP ... They are making unfortunately frequent use of the IDENTITY column in conjunction with Primary Key. ... IDENTITY has none of the properties of a data type because it is not a data type at all. ...
    (comp.databases.ms-sqlserver)
  • Re: Views vs Stored Procedures, whats the difference?
    ... In the proc I would write the MAX as you've done. ... @optional_parm1 int = NULL, ... While the above does contain logic, it will give you the best plan in MS SQL ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ...
    (comp.databases.ms-sqlserver)
  • Re: From varchar(max) to xml
    ... the "syscomments" references will need to be changed for SQL 2005 to ... ADD CONSTRAINT PK_Numbers PRIMARY KEY ... CREATE TABLE TestInput(INT PRIMARY KEY, ... DECLARE @temp_str VARCHAR ...
    (microsoft.public.sqlserver.xml)
  • Re: From varchar(max) to xml
    ... this thing together on SQL 2000. ... ADD CONSTRAINT PK_Numbers PRIMARY KEY ... CREATE TABLE TestInput(INT PRIMARY KEY, ... DECLARE @temp_str VARCHAR ...
    (microsoft.public.sqlserver.xml)
  • Re: Storing Users/Groups
    ... websites can interface with this setup. ... > returns @outtable table (root_node int, group_key int, primary key ... > --40 members are members of 50 ... PRIMARY KEY, ...
    (microsoft.public.sqlserver.programming)