Hello,
In many instances a stored procedure that returns a
resultset is usefull to provide summary fields. The
current implementation does this very well in most
circumstances, but when you try to join the procedures
resultset, with another dataset you can get some speed
problems. While there are ways around this in certain
circumstances, it is not always possible to optomize
it to run at a decent speed in all circumstances.
A possible solution for optimizing these joined
procedures is:
1) You see Statement A:
Select M.*, P.* from Master M, Proc P where
(Upper(M.Field)=Upper(P.Field));
2) Server chooses iterator field based on from highest
to lowest probable candidate.
1) Is a field in a table
2) Is a field in a non-optomizable procedure
3) Is a field in an optomizable procedure
In this case it would choose M.Field to be the
iterator.
3) Investigates whether or not joined procedure is
optomizable. A procedure is optomizable when a FOR
SELECT statement assigns a Value from the same table
and field that is being used in the query's where
clause to a resultset variable that is in use in the
query's where clause, and that resultset variable is
not modified beyond how the iterator field is modified
in the query's where clause before a SUSPEND is
reached, as shown in the following:

CREATE PROCEDURE Proc returns (Field Char(20), Field2
Char(20))
AS
DECLARE VARIABLE Cummulative CHAR(20);
BEGIN
FOR SELECT Upper(Field) from Master into :Field DO
//Optomizable
BEGIN
Cummulative='';
FOR SELECT Blah from yetanothertable where
Blah=Field INTO :Field2 DO //Non-optomizable
BEGIN
Cummulative=Cummulative+Field2;
END
Field2=Cummulative;
SUSPEND; //Field1 hasn't changed so its optomizable
END
END
4) If an optomizable query is found in the procedure
just plug in the value from the parent query that
contains it in the optomizable query's place, and
don't worry about iterating again through the
resultset in the procedure. The For Select syntax is
ambiguous as to whether or not its traversing
forwards, backwards, or randomly through a resultset
anyways and therefore anything coded with them should
not assume any kind of order particular order.

There are other Procedure optomizations that I can
think of but I don't want to make this message to
long, :-P.

Edward Flick

__________________________________________________
Do You Yahoo!?
Send instant messages with Yahoo! Messenger.
http://im.yahoo.com/