Why is the query running for so long?

[Ninel]

When I execute the following stored procedure it runs for about a minute.

CREATE PROCEDURE EquipmentListByProduct
(
@iProdTypeId int
)
AS

SET NOCOUNT ON
DECLARE @iError int, @iRows int

SELECT pn.prodTypeId, pn.prodId, pn.prodName
FROM prodNames pn
WHERE pn.prodTypeId = @iProdTypeId

SELECT @iError = @@ERROR, @iRows = @@ROWCOUNT
IF ( @iError <> 0 )
BEGIN
RETURN @iError
END

IF ( @iRows = 0 )
BEGIN
RETURN -1
END

RETURN @iError
GO


The table only has 22 records.
Do I need to index the table? If so how do I do this?

[JBane]

No, with 22 records, you don't need an index (though a good idea on all tables). Try it without the @@rowcount and @@error functionality to see if you can eliminate the query as the culprit.

Jeff

[Ninel]

Hi Jeff,
Long time no speak. Seems like I'm going to have problems every step of the way.

I put an index on the table and it returned the records right away.
But now through ASP using the following code it times out.


Set ObjCmd = Server.CreateObject("ADODB.Command")
Set rsEquipData = Server.CreateObject("ADODB.Recordset")

With ObjCmd
.ActiveConnection = cnConn
.CommandText = "dbo.EquipmentListByProduct"
.CommandType = adCmdStoredProc
.CommandTimeout = PAGE_TIMEOUT

.Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@iProdTypeId",adInteger,adParamI nput, 1)

Set rsEquipData = .Execute
End With

If rsEquipData.state <> 0 Then
Do Until rsEquipData.EOF
Response.write rsEquipData("prodName")
Loop
End If


What am I doing wrong now? And can this timing out issue kill the server?

[andi_g69]

In your loop you are missing the following statement:

rsEquipData.MoveNext

Without this statement you are outputting the first record eternally or until you hit the timeout.

The timeout protects your server from being killed

:-)

[Ninel]

Thank you sooooooo much.

I always forget that line of code.