BTW Helen, getting just a bit more technical...

(more inline)

> -----Original Message-----
> From: Helen Borrie [mailto:helebor@...]
> Sent: Thursday, November 22, 2001 2:57 AM
> ...
>
> If you are still talking about operating on rowsets back from
> stored procedure output, it is not that simple. Your procedure
> has to create that temp table on the local disk by processing a
> cursor first..and ACID transaction control is a joke.

It does not have to be a temp table. And temps are not that
different from other transactional situations where something
has to be rolled back and disappear.

Transactions (with multiple operations) controlled from the
client side look much more tricky to me.

> It also
> forces you to split up the logical task on the client side by
> having to deal with the SP input and output as unrelated
> data...

Why?
You just send an SQL statement to the server and get a rowset
back. The only difference is the SQL syntax: using
EXEC SPROC();
or
EXECUTE PROCEDURE SPROC();

instead of
SELECT * FROM STOREDPROC();

Claudio already explained this too.

It is only different when the Stored Proc returns multiple
rowsets, which is a bit more tricky and a lot LESS USED.

Notice however that I NEVER used returning multiple rowsets.
I always avoided that. But I used returning just one rowset
all the time.

In my wish list, I only have "returning a single rowset"!
=:o)


*** Now, this part is "just for fun" (even more than the
previous):


Not even multiple rowsets and temp tables are that different
in terms o technical challenges from multiple operation
transactions controlled from the client.

And I mean technical challenges like resource management,
with its pending data changes, lost connections, timeouts
and so on.

When a client times out you "just" have to rollback all
pending changes and cleanup the resources he allocated. This
is a common database feature and I do not know any database
that manages transactions better than IB/FB (although Oracle
is also attempting the versioning thing with 9i).

And temp tables are something that is always rolledback
even when the transaction was commited. Like a subtransaction
that always fails.


Besides, considering that a returned rowset has no impact on
the next, the server can just keep openning all those cursors
until the stored procedure returns and then just return them
back to the client in a single step. This means that such
Stored Procedure would have a SINGLE RESULT, albeit a
composite one, holding an array of cursors to the resulting
rowsets plus the usual status stuff and so.

Processing those cursors and rowsets is a similar problem to
processing a rowset from
SELECT * FROM table1, .., tableN WHERE <complex join>

For complex queries IB also needs to alocate temporary
resources and to clean them up. This is just like a client
that makes several queries at the same time.


Now, I now that it is easier said than done. What I mean is
that the most complicated stuff (a good transaction/resource
management infrastructure) is already in place.

And remember: this was "just for fun".

> it's horrendously slow...it contributes significantly to
> the major problem that SQLServer presents, of hanging up or
> falling over completely through unpredictable memory demand (or
> just a change in the air pressure!)...it adds more overhead to
> the MS haystack, with management of permissions on temporary
> objects...oh, I could go on...
> ...

I still think that what you talk about just has to do with sh*ty
architecture and sh*ty implementation more than with the
features themselves.


Have fun,
Paulo Gaspar