At 08:07 PM 09-09-01 +0000, you wrote:

>Helen Thanks
>
>I have got it working at last but it is incredibly slow !!
>I have three nested procedures and the innermost one does
>'getcount(col a) from mytable where colB= ,and colC= and colD= '
>the table has 15,000 records and I have to do this for each of 10
>values of col B and 1000 values of colC. So this amounts to 10,000
>goes of getcount(col a) before the result is available.
>
>Now when I did it directly from the client using queries I first
>reduced the table size by doing "insert into temptab select from
>mytab where colB =" (table now typically 1000 records)
>then reduced again " insert into temptab2 select from temptab where
>colC =" (table now typically 100 records)
>then select count( ) this was vastly faster over all.
>
>Can I do this sort of thing with stored procs ?
>But I was told this was not a proper way of doing it in any case !

Does you getcount() procedure do select count()? Select count() is expensive and the more times you do it, the more expensive it gets! As long as you are in a stored procedure anyway, why not track these counts in counter variables, so that you avoid having the rows being walked just to get a count?

Where are the input values coming from? Can they obtained from a query or a view? If so, you could run two FOR..SELECT..INTO loops, the outer one reading the input parameters and applying them to the inner one; and the inner one doing the counting and feeding values into output variables to be SUSPENDed into the output set. The counter variables just get updated when there is a "hit" on a selected row - no separate counts at all.

It would helpful to see what your output set should look like and also the structure of the contributing sets...

Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________