shamif
asked on
timeout expired
I get the following error because of a network issue we are having. We still havent 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 cant 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.
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 cant 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.
ASKER
I put with (nolock) but I am still getting timeout errors. I don't see how nolock can fix the problem.
Do you have an index on the fdItemOrderId field?
ASKER
no I don't
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Putting an index helped. The timeout error went to different stored procedure. I think we have to fix the root of the problem. But thank you for your help.
CREATE PROCEDURE spGetItemRackCounter
@fdRackType as int,
@fdItemOrderId as int,
@fdReturn1 int output
AS
Begin
SELECT @fdReturn1 = Count(fdAssemblyBarcode)
FROM tbStation5 with(nolock)
WHERE fdItemOrderId = @fdItemOrderId
IF @fdReturn1 is null
Select @fdReturn1 = 0
End
GO