Re: Views vs Stored Procedures, whats the difference?

Actually I think we are violently agreeing lol.

With your example I would never ever do that, mind you ask celko about doing
paging and he would! All formatting done on the client etc...

In the proc I would write the MAX as you've done.

In the MS space, we can use simple logic without having to resort to cursors
or sending the entire results back, back to the parameters example....

create proc myproc
@optional_parm1 int = NULL,
@optional_parm2 int = NULL
begin
if @optional_param1 is not null and @optional_param2 is not null
select max( blah )
from yourquery (or view)
where col1 = @optional_parm1
and col2 = @optional_parm2
else if @optional_param1 is not null
select max( blah )
from yourquery (or view)
where col1 = @optional_parm1
else if @optional_parm2 is not null
select max( blah )
from yourquery (or view)
where col2 = @optional_parm2
else
select max( blah )
from yourquery (or view)

end

While the above does contain logic, it will give you the best plan in MS SQL
Server, you could of used COALESCE but you would not get a good plan; you
could also build your SQL dynamically in the stored proc based on the
parameter inputs, parameterise and execute it.

In some respects we are agreeing, but i'm not sure about your stance on
using IF ELSE, I would not want to got to a one proc per parameter
combination model because its just too much overhead when we have IF ELSE to
lessen the support and development burden.

Tony

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"Serge Rielau" <srielau@xxxxxxxxxx> wrote in message
news:44kksgF2mgoiU1@xxxxxxxxxxxxxxxxx
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: 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: Get Primary Key column
    ... > Proc takes a table name and a value as input parameters, ... the tables only have 1 primary key and I ... > that there was a SQL name that I could use to refer to that column. ...
    (microsoft.public.sqlserver.programming)
  • 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: Views vs Stored Procedures, whats the difference?
    ... I'm just trying to differentiate between two fundamentally different SQL objects. ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ... SELECT MAXFROM nestedview ...
    (comp.databases.ms-sqlserver)