Marco van de Voort wrote 12 lines
> Michael Van Canneyt wrote:
>
>
>> My point of view is that a database is for storage, not for logic...
>>
> Ah, this is basic use of resources. The benefit of Stored Procs is speed
> of execution.
>
For dialy routines, only if they are not complex, and significantly reduce the amount of data
transfered tuples (the result set), and it can not be expressed in a query.
I don't doubt what you are saying, but in my experience, this is not the case. It really depends on the client and underlying protocol used to access the RDBMS. In a local database system, yeah, probably. In a RDMBS, no.
With complex I mean is that if the stored proc needs to use temp tables or
other state that consists out of a set of tuples, then it is usually slower.
Again, not true in my experience. Writing the same code on the client that manipulates data from multiple tables and uses the resulting dataset is a lot slower. Using Interbase/Firebird, you can create stored procs that return datasets in a ad hoc fashion (multithreaded) or bulk. Something like:
create proc test ()
returns (a int, b int, c int, d int, e int)
for select a, b, c, d
from TZ
into :a, :b, :c, :d do
begin
e = 0;
if (b in [1, 2, 5, 200]) then
begin
e = 22;
suspend;
end
else if (c = 250) and (b < 5) then
begin
e = 25;
suspend;
end
else if (a > 250) and (b = 5) then
begin
e = 8;
suspend;
end
else if (d = -1) then exit;
end
(forgive my syntax mistakes above, not done any Interbase for over a year.)
is far better than (pseudo code)
var
table : table_class;
begin
...(some construction or whatever)
table.execsql("select * from TZ")
while (not table.Eof) do
begin
a := table.fieldbyname('a').AsInteger;
b := table.fieldbyname('b').AsInteger;
c := table.fieldbyname('c').AsInteger;
d := table.fieldbyname('d').AsInteger;
e = 0;
if (b in [1, 2, 5, 200]) then
begin
e = 22;
suspend;
end
else if (c = 250) and (b < 5) then
begin
e = 25;
suspend;
end
else if (a > 250) and (b = 5) then
begin
e = 8;
suspend;
end
else if (d = -1) then exit;
end
end;
end;
Oh, and CPU is cheap compared to DB licenses nowadays.
Aha, the Microsoft defense! ;-) Chewbaka was a Wookie, don't you know? ;-)
