.NET SqlClient - Timeout expired

Question

Currently i am building an query on a database in .net core. One of the querys has many different filters like the following example:

IQueryable<OperatingInstructionDTO> newQuery = this.Database.Set<OperatingInstructionDTO>()
                .Include(x => x.CurrentState)
                .Include(x => x.Plant)
                .Include(x => x.Department)
                .Include(x => x.Creator)
                .Where(x => x.Type != null && x.Type.Equals(type) && x.Department.Id == user.Department.Id);

            
            if (filter.InstructionTitle != null)
                newQuery = newQuery.Where(x => x.Title.Contains(filter.InstructionTitle));

            if (filter.SelectedStates != null)
                newQuery = newQuery.Where(x => filter.SelectedStates.Contains(x.CurrentState.Description));

            if (filter.SelectedEditors != null && filter.SelectedEditors.Count == 1)
            {
                if (filter.SelectedEditors[0].Equals("Eigene"))
                    newQuery = newQuery.Where(x => x.Creator.Email.Equals(user.Email));
                else
                    newQuery = newQuery.Where(x => !x.Creator.Email.Equals(user.Email));
            }

            if (filter.OverallTextFilter != null)
                newQuery = newQuery.Where(x =>x.Content!=null && x.Content.Contains(filter.OverallTextFilter));

            newQuery = newQuery.OrderByDescending(x => x.CreationTimestamp).ThenBy(x => x.Title).Take(filter.SelectedNumberOfResults);

            return newQuery.Select(x => new OperatingInstructionDTO
            {
                CreationTimestamp = x.CreationTimestamp,
                CurrentState = x.CurrentState,
                Department = x.Department,
                ParentOperatingInstruction = new OperatingInstructionDTO
                {

                },
                Plant = x.Plant,
                Title = x.Title,
                Id = x.Id,
                Creator = x.Creator,
                Type = x.Type
            }).ToList();

If i set the filter OverallTextFilter the execution is very slow. I know this but this feature is needed for the customer. Because the exection is so slow i get the following error message:

System.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
  Source=Core .Net SqlClient Data Provider
  StackTrace:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ModulareArbeitsanweisung.Database.Wrapper.InstructionManager.ReadOperationInstructionsWithFilter(UserDTO user, String type, InstructionFilter filter) in C:\Users\adlerlu\Desktop\CUE_GIT_REPO\SWP-Core-Backendservices\Modulare Arbeitsanweisung\ModulareArbeitsanweisung\ModulareArbeitsanweisung\Database\Wrapper\InstructionManager.cs:line 119
   at ModulareArbeitsanweisung.Controllers.InstructionController.List(InstructionFilter filter) in C:\Users\adlerlu\Desktop\CUE_GIT_REPO\SWP-Core-Backendservices\Modulare Arbeitsanweisung\ModulareArbeitsanweisung\ModulareArbeitsanweisung\Controllers\InstructionController.cs:line 64
   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__12.MoveNext()

Inner Exception 1:
Win32Exception: Der Wartevorgang wurde abgebrochen

How can i fix this?

@LeiYang I don't like the idea. I would like to build my database-logic in the code as well. (Single Source of Truth). Whats the benefit of a stored procedure? Like you see in my example there are many different filters. — Lukas Hieronimus Adler, Jul 06 '21 at 09:12
how many chars are there in average `.Content`, and have you checked the generated sql? — Lei Yang, Jul 06 '21 at 09:13
Its completly dynamic. Sometimes only a few (30 chars) but there are also content examples with thousands. — Lukas Hieronimus Adler, Jul 06 '21 at 09:14
tried [CREATE FULLTEXT INDEX](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-index-transact-sql?view=sql-server-ver15)? — Lei Yang, Jul 06 '21 at 09:15
Not at the moment. Is there no way to increase the command-timeout or something like this? — Lukas Hieronimus Adler, Jul 06 '21 at 09:16
[How to set CommandTimeout for DbContext?](https://stackoverflow.com/questions/10549640/how-to-set-commandtimeout-for-dbcontext) — Lei Yang, Jul 06 '21 at 09:17
@LeiYang posting suggestions at random doesn't help. A stored procedure won't make a slow query run any faster. A full-text-search index only accelerates FTS functions, not `LIKE '%abc%'`. It doesn't matter how long `Content` is, the `Contains` condition can't use any index so it will scan the entire table. In that case it's the number of rows that matters — Panagiotis Kanavos, Jul 06 '21 at 09:20
@LeiYang Your link also shows a .NET example. But i am using .NET CORE — Lukas Hieronimus Adler, Jul 06 '21 at 09:23
@LukasHieronimusAdler what is the *actual* query that gets executed? Are the fields indexed? Does the query use expressions that can't use indexes? `x.Title.Contains(filter.InstructionTitle)` for example will generate `WHERE Title like '%blahblahblah%'` which can't use any index and has to scan the entire table for matches. `StartsWith` on the other hand generates `LIKE 'blablah%'` which *can* use indexes because it's essentially a range search - every string between `blablah` and `blahblai` — Panagiotis Kanavos, Jul 06 '21 at 09:24
@LukasHieronimusAdler `CurrentState.Description` will have to be indexed to produce good performance too. `filter.SelectedStates.Contains(x.CurrentState.Description)` generates `description in ('a','b','c'...)`. With an index, that's a straightforward seek. Without it, once again the entire table will have to be scanned — Panagiotis Kanavos, Jul 06 '21 at 09:28
@Lukas Hieronimus Adler - have you tried extracting actual sql select (using sql trace), run it in ssms and check actual execution plan? — yob, Jul 06 '21 at 14:00
@LukasHieronimusAdler check this to set timeout https://stackoverflow.com/questions/39058422/how-to-set-command-timeout-in-aspnetcore-entityframeworkcore — FireAlkazar, Jul 06 '21 at 16:35

score 2 · Answer 1 · answered Jul 07 '21 at 22:42

I would strongly caution your customer about this type of searching. Even if you extend the timeout you have opened the door for users to accidentally or intentionally cripple your system with expensive queries. Users are fickle beasts. When something takes too long they have a tendency to refresh the browser and try the same query or adjusted queries again and again until it works, or open new tabs to trigger queries.

For text searches wherever possible you should support only BeginsWith type searches, potentially offering a Contains as an optional last resort. (I.e. either a selection of "Begins With" (default) vs. "Contains", or a "Full text search" checkbox to send /w the search request)

When it comes to text searching you should also consider enforcing a minimum search term length (client and server-side), as well as server-side pagination for your results. Nothing is stopping a user from doing a search "WHERE Comments LIKE '%a%'" and the potential performance and memory usage consequences of that. You may expect users to search for something that might return a reasonable number of rows, but as systems mature, the total data size can grow considerably and this leaves a door open to overload a web server / database with large result sets.

In enterprise systems where this type of searching is a requirement, along-side things like potentially expensive broad reaching reporting, you should ideally have a replicated reporting database instance. For expensive searches it can be beneficial to run the searches to retrieve summary data from the reporting replica rather than the read/write database instance. (Provided the reporting replication is near real-time) This keeps things like row locking to avoid dirty reads from impacting day-to-day operations against the records. Expensive searches and reports should also be brokered via a queuing mechanism or similar to avoid too many expensive requests being processed at once. This can be facilitated by a polling loop from the client or callback mechanism like SignalR.

I.e.

Client code sends search request data
Server queues the search request data for a worker pool to pick up first come first serve and returns SearchQueued response with a SearchID to client.
Client waits n seconds, requests results using the SearchID.
Server sends back Search Results if ready, SearchQueued/InProgress response or SearchNotFound.
Client waits n more seconds and repeats request with SearchID.

This would ideally include an implementation for a cancellation token if the user wants to abort the search to try something else or leaves the page to dequeue the request if not started, or tell a processing worker to abort the query.

.NET SqlClient - Timeout expired

1 Answers1