Why is there a timeout in a simple select

3
votes

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.

2
c#sqlsql-serversqlexception
A lot depends on what the function ActivityView('de') is doing. Does a select on select * from ActivityView('de') also time out? – Gordon Linoff
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
ActivityView('de') is a simple view which uses 'de' as a part of a join. – BennoDual
I'm sorry to see that you are using GUIDs for ids. – siride
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 (which has gotten me in the past)promoting the text in the datatable to a uniqueidentifier and not using your indexes. – Scott Chamberlain

2 Answers

2
votes

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.

0
votes

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.