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?
Topounceis 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 doesTopouncetake to run normally? Is there any chance it is being impacted by differentSETconditions from the ADO.NET code (theSETconditions can prevent things like persisted+calculated+indexed columns from working correctly, forcing a table-scan instead of an index-seek, for example)SET STATISTICS TIME ONto see how long it takes.