Timeout expired error

[shaminda]

I get the following error because of a network issue we are having. We still haven’t figured out where the problem is. Here is the error:

“Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”

I always get the error in the following stored procedure:

CREATE PROCEDURE spGetItemRackCounter
@fdRackType as int,
@fdItemOrderId as int,
@fdReturn1 int output
AS
Begin
SELECT @fdReturn1 = Count(fdAssemblyBarcode)
FROM tbStation5
WHERE fdItemOrderId = @fdItemOrderId
IF @fdReturn1 is null
Select @fdReturn1 = 0
End
GO

I can’t figure out why I get the error only when the program is accessing this stored proc but not the other stored procedures. I have about 20 stored procedures accessing tbStation5 table. And altogether about 60 stored procedures in this database. There are also 297772 records in tbStation5 table. What can I do to improve this stored procedure or table? If I add an index where should I add it to? Will that make things better?

Also the reason I say it is a network problem is because I get the error at certain times. Every day between 8:00 AM – 9:00 AM and 12:00 PM 1:00 PM, and around 2:00 AM.

 

[gmmastros]

Run this...

[tt][blue]sp_helpIndex 'tbStation5'[/blue][/tt]

And then post the output here.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[shaminda]

PK_tbStation5 clustered, unique, primary key located on PRIMARY fdStation5Id


UXStn5AssemblyBarcode nonclustered, unique, unique key located on PRIMARY fdAssemblyBarcode

 

[gmmastros]

I suggest that you create an index. Before doing this, run the stored procedure and take note of how long it takes to run.

Then create this index (Copy/Paste this to Query Analyzer).

Create NONCLUSTERED Index idx_tbStation5_fdItemOrderId
on tbStation5(fdItemOrderId) ON [Primary]

Then, re-create the stored procedure (by changing Create to Alter.

[!]Alter [/!]PROCEDURE spGetItemRackCounter
@fdRackType as int,
@fdItemOrderId as int,
@fdReturn1 int output
AS
Begin
    SELECT @fdReturn1 = Count(fdAssemblyBarcode)
    FROM tbStation5
    WHERE fdItemOrderId = @fdItemOrderId
    IF @fdReturn1 is null
    Select @fdReturn1 = 0
End
GO

Now, run the stored procedure again. Is it any faster?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom