Menu
I'm running an ASP.NET Web Pages page that upon initial load pulls a list of items from a SQL server. This query runs in a second or so and loads the page within 2 seconds. The return is about a 1000 records, give or take. I'm pulling Hostnames from a Service Manager SQL database along with some other information.
Within this page, I have a search built in that essentially runs the exact same query but runs it with a LIKE based on hostname. This loads the same page with all hostnames that are contain part of the search query. The query generally runs within SQL Management Studio in under a second, but loading the page takes substantially longer and sometimes it times out.
My question is, why does the parameter based search takes so much longer and sometimes timeout for no apparent reason. Are there any steps that can be taken to mitigate this timeout? Below is the full error.
Server Error in '/' Application.
The wait operation timed out Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:
System.ComponentModel.Win32Exception: The wait operation timed out Source Error: Line 13: } Line 14: Line 15: var selectedData = db.Query(selectCommand, searchTerm); Line 16: Line 17: Source File: c:\Users\u0149920\Documents\My Web Sites\AppSupport\servers\default.cshtml Line: 15 Stack Trace:
[Win32Exception (0x80004005): The wait operation timed out] [SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1753346 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5295154 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +242 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1682 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +59 System.Data.SqlClient.SqlDataReader.get_MetaData() +90 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +365 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +1325 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +175 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +134 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41 System.Data.Common.DbCommand.ExecuteReader() +12 WebMatrix.Data.<QueryInternal>d__0.MoveNext() +152 System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +381 System.Linq.Enumerable.ToList(IEnumerable`1 source) +58 WebMatrix.Data.Database.Query(String commandText, Object[] parameters) +103 ASP._Page_servers_default_cshtml.Execute() in c:\Users\u0149920\Documents\My Web Sites\AppSupport\servers\default.cshtml:15 System.Web.WebPages.WebPageBase.ExecutePageHierarchy() +197 System.Web.WebPages.WebPage.ExecutePageHierarchy(IEnumerable`1 executors) +69 System.Web.WebPages.WebPage.ExecutePageHierarchy() +151 System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage) +76 System.Web.WebPages.WebPageHttpHandler.ProcessRequestInternal(HttpContextBase httpContext) +114 Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.17929
258
public class SQLTimeoutException extends SQLTransientException. The subclass of SQLException thrown when the timeout specified by Statement has expired. This exception does not correspond to a standard SQLState.
ComponentModel. Win32Exception is the most basic exception type that will occur within your . NET applications when something goes wrong while using internal win32 -style operating system calls. These can vary from invalid path and file not found errors to network address issues and resource management problems.
The problem you are having is the query command is taking too long. I believe that the default timeout for a query to execute is 15 seconds. You need to set the CommandTimeout (in seconds) so that it is long enough for the command to complete its execution. The "CommandTimeout" is different than the "Connection Timeout" in your connection string and must be set for each command.
In your sql Selecting Event, use the command:
e.Command.CommandTimeout = 60 for example:
Protected Sub SqlDataSource1_Selecting(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) e.Command.CommandTimeout = 60 End Sub If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
