Sql Server 2005 Timeouts With .NET Adapters

I am running a query that returns ~30,000 rows from a C# TableAdapter, and often times I get an error like:

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

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)\r\n   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior 开发者_Python百科cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

If I run the same query with less rows returned, I won't get the error. And if I run the exact same query that the TableAdapter in C# runs in SSMS myself, the query of 30,000 rows returns fine.

What could cause this?

Edit: The query in SSMS takes ~7 seconds

---

You should extend the time out period of the query to get results. As the result set is huge, query may take some time to complete. Most Probably the time out period is expiring before results are generated

you can use SQLCommand's CommandTimeout property to set the time to expire. Set it to a large time for testing purpose and check whether you get the results or not

---

This could be a problem with indexes. Especially since you said the query runs in SSMS but not in code. See this article which has more information (and is a good read IMO):

http://www.sommarskog.se/query-plan-mysteries.html

---

How long does the query take? Take the SQL and run it directly in the database and how fast is it? It may need to be turned with an index or other tuning method if it is taking a long time.

You can increase the time out but why 30,000 records returned. If this isn't a background process, the query should be set up to be paged to return X records at a time.

If you absolutely need 30,000 records:

User a DataReader, its faster and less resource intensive than a data adapter, just bear in mind it uses a forward only (fire house) approach to data retreival.

Pull records in chunks at a time, for example pull 1000, process, pull 1000, proceess...

Up the timeout in .Net Tune the Query to make it run faster

Consider caching if the 30,000 records if the data is not too volatile

---

This is speculative but you might be encountering an instance of parameter sniffing causing problems.

http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/

The quick fix is to add local variables, copy your parameter values to the local variables and then use the local vars in the query. From the article above

CREATE PROCEDURE MyProcedure
     @UserName nvarchar(20)
 AS

 BEGIN
     DECLARE @myUserName nvarchar(20)
     SET @myUserName = @UserName
     -- Insert statements for procedure here
     SELECT DisplayName, FirstName, LastName 
     FROM dbo.User
     WHERE UserName = @myUserName

END

More info on what is actually happening here: http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html