1

I have stored procedure Topounce that gets the top record from a dynamic table and speaks it out of the PC. When the there are too many records it times out in

SqlDataReader dr2 = select.ExecuteReader() 

Right now there are around 750 records. I already tried CommandTimeOut = 0 and it took around 10 min for the record to be spoken. Is there a way around this?

Here is the error:

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

Code:

try
{
   using (SqlConnection connStr2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString))
   {
      //Selects top record from vwAnno
      SqlCommand select = new SqlCommand("Topounce", connStr2);
      select.CommandType = CommandType.StoredProcedure;                   
      select.Parameters.AddWithValue("@ID", (string)Num);

      connStr2.Open();

      SqlDataReader dr2 = select.ExecuteReader();   // TIMES OUT HERE

      //Reads record in vwAnnounce
      while (dr2.Read())
      {
          //do work
      }

      dr2.Close(); //Close Datareader connection
      connStr2.Close();
}

UPDATE here is the store procedure.

     USE [Queue]
      GO
      /****** Object:  StoredProcedure [dbo].[TopRowViewAnnounce]    Script Date:         06/01/2013 11:55:50 ******/
       SET ANSI_NULLS ON
     GO
     SET QUOTED_IDENTIFIER ON
     GO
     ALTER PROCEDURE [dbo].[TopRowViewAnnounce] 
    @QueueID int

    AS


    BEGIN

 SELECT TOP 1 id, qdate, ticket_number, QID, received, displaynum, station, transcodevoiced FROM vwAnnounce WHERE QID = @ID ORDER by received ASC
    END

The reason I did a store procedure its because I thought it was going to help the timeout. I originally had it like this.

SqlCommand select = new SqlCommand("SELECT TOP 1 id, qdate, ticket_number, QID, received, displaynum, station, transcodevoiced FROM vwAnnounce WHERE QID = @ID ORDER by received ASC", connStr2);
  select.CommandType = CommandType.StoredProcedure;                   
  select.Parameters.AddWithValue("@ID", (string)Num);

  connStr2.Open();

  SqlDataReader dr2 = select.ExecuteReader();   // TIMES OUT HERE

I still havent got the solution. Anybody?

7
  • 2
    can you post the stored procedure code ? 750 records is nothing it should not be that slow. Commented Jun 1, 2013 at 6:00
  • That is correct @SenadUka. 750 records are nothing. Commented Jun 1, 2013 at 6:09
  • 2
    It sounds to me like Topounce is the real problem here, not the data volume - unless, that is, the rows include large text/binary blobs (as in: muchos muchos bytes). How long does Topounce take to run normally? Is there any chance it is being impacted by different SET conditions from the ADO.NET code (the SET conditions can prevent things like persisted+calculated+indexed columns from working correctly, forcing a table-scan instead of an index-seek, for example) Commented Jun 1, 2013 at 6:13
  • @MarcGravell, I knew there is a vast room for query optimization with the stored procedure when I saw it took 10 minutes to return records. My queries on a 10 million records table takes roughly 200 milliseconds to return 1000 records. Commented Jun 1, 2013 at 6:17
  • 1
    Try running the same query in SQL Server Management Studio with option SET STATISTICS TIME ON to see how long it takes. Commented Jun 1, 2013 at 6:18

1 Answer 1

3

Add Connect Timeout=120 in your connection string.

By default connection time out is 30 secs.

Since, it is retrieving large number of records , you will need to increse that time in your accordance.

You Connection String can be:

data source=ServerName;initial catalog=DBName;uid=ID;pwd=Password;Connect Timeout=120

On IIS also you have timeout setting.

IIS > Website tab > Connection time out box .

Set the max timeout value (in secs) IIS should maintain idle connection .

Hope its helpful.

Sign up to request clarification or add additional context in comments.

7 Comments

If I am not wrong, Connect Timeout is different than Command Timeout. Plus problem seems to be in the stored procedure.
The timeout problem is with executing the query - the connect timeout does not influence that in any way, shape or form. This is a timeout if connecting to the database is timing out...
@JayPatel nope, since i deal with large DBs of share market, i have faced this problem many times. When we increase connection time out, problem gets solved.
@marc_s let him try this once, i am sure, problem will get solved.
@Freelancer, but is it the best solution even if it did work? I think solving the core problem of inefficient query is the real solution.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.