At 12:45 AM 13/05/2005 +0000, you wrote:

>I have been creating stored procedures using the FOR - SELECT - DO
>constructs but find it sometimes slow for large databases (for schools).

Poorly performing, looping SPs often display a lot of extraneous logic.
Looping cleanly through a cursor set on the server side should never be
slow. However, any SELECT calls inside a SP are no less prey to poor
indexing than those from clients! If you are looping through joined sets,
there may be avoidable agony in the choice of search criteria for the joins.

>What I discovered to be faster is incorporating the IN predicate in
>joined queries when the key fields to select are known and limited to
>a few values ( e.g. -
>SELECT a.* FROM studentTable a INNER JOIN ..... WHERE a.studentId IN
>(1009,2220,5678) AND ....

What makes this specific syntax fast for small lists, in this case, is the
fact that the search is doing an OR-ed indexed search for each value in the
list. The longer the list, the more searches. This syntax restricts you
to 1000 list members.

Don't use SELECT * in SPs, since, in SPs, you are always passing column
values to variables in a strict order.

>I want to do the same thing this time in stored procedures. I tried
>to select the values in a first query but can't get the second query
>using the IN predicate to work. I've used a string variable to store
>the values selected in the first query and feed it into the IN clause.
>How can I implement this?

You could implement a form of list-processing, by writing a second (i.e.
first!) SP that processes the string. At each iteration of the loop, you
call this SP, and have it return the leftmost value and then, in your loop,
"behead" the input string for the next iteration of the loop.

Assuming your input string is presented as a comma-separated list with no
comma following the last list member....

create procedure getlistitem (inlist varchar(500))
returns (currentval integer)
as
declare currentstring varchar(9) = '';
declare currentchar char = '';
begin
currentval = 0; /* or -99999 or whatever you want a non-value to be */
while (currentchar is not null) do
begin
currentchar = substring(inlist from 1 for 1);
if (currentchar = '' or currentchar = ',') then
currentchar = null;
if (currentchar is not null) then
currentstring = currentstring || currentchar;
end
if (currentstring <> '') then
currentval = cast (currentstring as integer);
end

Then, in your main (second) procedure:

create procedure blah (inlist varchar(500))
returns (var1..... etc.) as
declare AllDone smallint = 0;
declare CurrentKey integer = 0;
declare CurrentChar char = '';
begin
while (AllDone = 0) do
begin
execute procedure getlistitem(:instring)
returning_values(:CurrentKey);
if (CurrentKey > 0) then
begin
/* assuming here that your query returns only one row. If not,
you will have to use the FOR loop. */
/* FOR */
SELECT a.val1, etc. FROM studentTable a
JOIN bTable b
ON b.studentId = a.studentId
WHERE a.studentId = :CurrentKey
INTO :val1, ...........etc. ;
/* DO and omit the terminator */
SUSPEND;
CurrentKey = 0;
while (CurrentChar <> 'X') do
begin
CurrentChar = substring(inlist from 1 for 1);
if (CurrentChar = ',') then CurrentChar = 'X';
if (CurrentChar is not null and CurrentChar <>'') then
inlist = substring(inlist from 2);
else
CurrentChar = 'X');
end
CurrentChar = '';
end
if (inlist is null or inlist = '') then
AllDone = 1;
end
end

This is just to give the idea - if you do something similar, make sure you
test the logic (mine and yours!) thoroughly.

./heLen