Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

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.
0
shamif
Asked:
shamif
  • 3
  • 3
1 Solution
 
chapmandewCommented:
try this:

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
0
 
shamifAuthor Commented:
I put with (nolock) but I am still getting timeout errors. I don't see how nolock can fix the problem.
0
 
chapmandewCommented:
Do you have an index on the fdItemOrderId  field?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
shamifAuthor Commented:
no I don't
0
 
chapmandewCommented:
You definetly want one on there if you're searching through a lot of records....it may not fix everything, but it is going to help.

create nonclustered index tbStation4_OrderID on tbStation5(fdItemOrderId)
0
 
shamifAuthor Commented:
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.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now