Posted by Tony Rogerson on 02/05/06 10:02

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

[Back to original message]