Hello,

My First question is:

I am Writing a search engine and have written a stored procedure that works very well with small Databases.

I was wondering if anyone could shed some light on how to improve the
performance of not only this procedure but other procedures using the same logic.

When searching for criteria I want to return a result set with the typical "1 to 10 of 3000" type format.

The procedure looks something like this:

CREATE MyProc(Input_1, FromRow int, RowCount int)
Output(Output_1, TotalRows Int)

AS
Declare variable vCount Int;
BEGIN
vCount = 0;

Select Count(*) From TABLE WHERE (Where criteria)
into TotalRows;

For Select * From TABLE WHERE (Where criteria)
into Output_1
do
Begin
vCount = vCount + 1;
If (vCount >= FromRow) and (vCOunt <= (FromRow+RowCount)) then
suspend;
End

END


My Second question is:

How do you include a Count(*) within a Select procedure, when the Count(*) is not the only criteria eg.

Select *, Count(*) From TABLE WHERE (Where criteria) This obviously doesnt work but how would you get this kind of result?

----------

****** Message from InterScan E-Mail VirusWall NT ******

** No virus found in attached file noname.htm
** No virus found in attached file noname.htm

Virus scanned By Foscom Mail Server
***************** End of message ***************



----------

DISCLAIMER:
This email is confidential and intended solely for the use of the individual to whom it is
addressed. Any views or opinions presented are solely those of the author and do not
necessarily represent those of Foscom cc.
If you are not the intended recipient, be advised that you have received this email in
error and that any use, dissemination, forwarding, printing, or copying of this email is
strictly prohibited.
If you have received this email in error please notify Foscom cc. -
by telephone on +27 (0) 11 708-3646, or by return E-mail
We do not accept any responsibility or liability for damage caused by viruses
or any other content contained in this message.



[Non-text portions of this message have been removed]