Why is there a timeout in a simple select

Question

I have an application which uses SqlCommand.ExecuteReader() to read data from a SQL Server 2008 database. When I use this, I will get a TimeoutException. The CommandTimeout is set to 40s - it takes also 40s till the exception occurs. I have also set the IsolationLevel to ReadUncommitted. When I execute the query directly in SQL Server Management Studio, it will be executed fast (1s) without any problems.

The query is relatively simple:

select TOP (50)  * 
from ActivityView('de') a 
where IsTrashed = 0 and 
   (Subject Like '%' and 
            (a.InchargeId = 'e9584e24-090a-4ef3-a048-330399bfb1b0' or a.PrivilegeLevelId in ('eb39d6bf-4904-4b8d-942f-39463c5d3c3f') or 
            (select top 1 Id from ActivityContact x where x.ActivityId = a.Id and x.UserId = '61423b82-bd6a-4381-808a-6df42f02ab59' ) is not null)) 
 order by a.[Key] desc

When I execute this, I get the fallowing exception:

   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()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
   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 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()

But when I execute the statements directly in SQL Server Management Studio, it is working perfectly with no error.

A lot depends on what the function `ActivityView('de')` is doing. Does a select on `select * from ActivityView('de')` also time out? — Gordon Linoff, Sep 15 '13 at 12:11
Mmhh - I haven't tested this. But why is there a timeout with my app and why is it very fast directly in Management-Studio? - I mean, the timeout is occuring after the command is executed on sql server and it comes not back after 40s. Isn't this part of executing the same as it is with Management Studio? — BennoDual, Sep 15 '13 at 12:25
ActivityView('de') is a simple view which uses 'de' as a part of a join. — BennoDual, Sep 15 '13 at 12:26
In your database are those guids being stored as `uniqueidentifiers` or as strings (`varchar`, `char`, `nvarchar`, ect.)? If they are, and you are using parameteriezed queries and passing in `Guid` objects you may be getting bit by [Data Type Precedence](http://msdn.microsoft.com/en-us/library/ms190309.aspx) (which has [gotten me in the past](http://stackoverflow.com/questions/7637907/query-extremely-slow-in-code-but-fast-in-ssms))promoting the text in the datatable to a `uniqueidentifier` and not using your indexes. — Scott Chamberlain, Sep 15 '13 at 18:54

Mauricio Gracia Gutierrez · Accepted Answer · 2013-09-15T14:25:13.353

2

Since you have a SQL-command that is constructed based on what the user selects as filters you need to generate a more efficient SQL command based on those selections

So if the user did not select a Subject, dont use Subject Like '%' just dont include that in your query. Keep in mind that any record in your table will match that condition and the LIKE operator is very time consuming

Apply this idea, of just including the filters that have some criteria, to all the posible filters that your command can have.

edited Sep 15 '13 at 14:25

answered Sep 15 '13 at 11:40

Mauricio Gracia Gutierrez

10,288
6
68
99

Hi Mauricio - Subject Like '%' is in the query, because the user doesn't give in a searchvalue - I think, sql server (the optimizer) will remove it? – BennoDual Sep 15 '13 at 11:43
Yes, the SQL command is based on user filters - there can be more as one value for PrivilegeLevelId – BennoDual Sep 15 '13 at 11:48
@ThomasKehl I have updated my answer based on your comments. basically if the user did not select any criteria for a filter dont include it in your query – Mauricio Gracia Gutierrez Sep 15 '13 at 13:47
Also, there is no LINQ-to-SQL in the question or in the stack trace. – siride Sep 15 '13 at 14:17
@siride thanks for pointing that out, if you have an answer feel free to post it – Mauricio Gracia Gutierrez Sep 15 '13 at 14:24
@MauricioGracia: I don't, unfortunately, though I had some ideas that didn't pan out. – siride Sep 15 '13 at 14:25

score 0 · Answer 2 · answered Dec 01 '16 at 07:33

For me, the cause of TimeOut Exception is very strange.

I create and open a SqlConnection each time a query is executed. However, I don't call connection.Dispose() at the end of query (although I call connection.Close()). When I restart the SQL Server instance service the error disappears, but after few executions, the error is back again.

I added connection.Dispose() to the end of query and everything worked fine.

Why is there a timeout in a simple select

2 Answers2