System.Data.SqlClient.SqlException (0x80131904): Timeout expired.

0 Answers 832 Views
General Discussions
Sergiy
Top achievements
Rank 1
Sergiy asked on 07 Apr 2017, 07:48 PM

We are using Telerik Reports 10.2.16.914

In the attachment you will see errors we are getting. 

here is the content of Output(Debug)

An exception has occurred while processing 'graph1' item:
System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlConnection.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, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   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 Telerik.Reporting.Processing.Data.SqlDataEnumerable.<GetEnumerator>d__0.MoveNext()
   at Telerik.Reporting.Processing.Data.SeedDataAdapter.GroupData(IEnumerable`1 rawData)
   at Telerik.Reporting.Processing.Data.SeedDataAdapter.Execute(IEnumerable`1 data)
   at Telerik.Reporting.Processing.Data.ResultSetAdapter.Execute(IEnumerable`1 data)
   at Telerik.Reporting.Processing.Data.MultidimentionalDataProvider.Execute(MultidimensionalQuery query)
   at Telerik.Reporting.Processing.DataItemResolveDataAlgorithm.GetDataCore(IDataSource dataSource, MultidimensionalQuery query, IServiceProvider serviceProvider, EvalObject expressionContext, IProcessingContext processingContext)
   at Telerik.Reporting.Processing.DataItem.GetDataCore(IDataSource dataSource, MultidimensionalQuery query)
   at Telerik.Reporting.Processing.DataItemResolveDataAlgorithm.ResolveData(String processingId, InMemoryState inMemoryState, MultidimensionalQuery query, Func`1 getDataCore, EvalObject expressionContext)
   at Telerik.Reporting.Processing.DataItem.ResolveData()
   at Telerik.Reporting.Processing.DataItem.ProcessItem()
   at Telerik.Reporting.Processing.ReportItemBase.ProcessElement()
   at Telerik.Reporting.Processing.ProcessingElement.Process(IDataMember dataContext)

We use web report viewer, it is telerikReportViewer-10.2.16.914.js

This is a part where we believe that timeout was increased. 

 public partial class LeastActiveDispatchers_Avg : Telerik.Reporting.Report
    {
        public LeastActiveDispatchers_Avg( )
        {
            InitializeComponent( );
            endpointSummaryDataSource.CommandTimeout = 600;
        }
    }

 

It is fills that the real that report is using timeout from 15 to 30 seconds instead of 10 minutes. 

The break point is not being hit when running in debug mode. So I have two questions I need help. 

1. How to debug a report? 

2. How to set timeout?

David
Top achievements
Rank 1
Veteran
commented on 07 Apr 2017, 08:33 PM

A database timeout is not really an error but more of a statement.  By default a given SQL query returns a timeout if no rows are returned, from the database, within 30 seconds of the command being initiated.  Once data is received the amount of time it takes to render a report varies greatly by the complexity of your report.

I see that you have overloaded the Report object to make the timeout longer.  Have you evaluated the underlying queries to see if you have a tuning problem?  You also might run into a database locking issue waiting for some other user to commit their data.  Damaged indexes might cause your query to suddenly be performing a full table scan rather than using the expected indexes.

I do not know what your end product looks like but I can attest to the performance of the Telerik suite.  I recently re-wrote a report that has 7 sub-reports.   It takes about 45 seconds of database activity, 90 seconds to render and yields about 400 pages of printed text.  

 

Sergiy
Top achievements
Rank 1
commented on 10 Apr 2017, 12:12 PM

@David

Thanks for your post. 

The report is using stored procedure. it takes 10-30 seconds to execute. We are not at the position to refactor the report.

Stef
Telerik team
commented on 12 Apr 2017, 02:11 PM

Hello Sergiy,

If the error message is displayed in the viewer's area, this implies the data-retrieval method has failed. test running SQL Profiler on the database to check which SQL query slows down the data-retrieval and prevents the database from responding in time. Verify the application has read/write access to the SQL Service (based on the Application pool's identity).

Please test also extending the executing time:
<httpRuntime executionTimeout = "number(in seconds)"/>


@David: Thank you for helping.

Regards,
Stef
Telerik by Progress
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
David
Top achievements
Rank 1
Veteran
commented on 13 Apr 2017, 09:00 PM

I guess I should point out that a stored procedure executed in the SQL Server Mangement Studio might takes seconds to execute however the same stored procedure might take minutes or longer to execute from the "client".    There is something called "Parameter Sniffing" which causes performance issues when run from the client.  This problem becomes very apparent if your stored procedure also calls one or more user functions within its actions.  When calling functions, I have had to add things like "OPTION (RECOMPILE)" to statements in the stored procedure.  The performance differences are amazing.  While I do not claim to be an "expert" on SQL tuning, I have had some good teachers.  Setting the "ARITHABORT" setting properly has also been shown to improve transaction performance.  (SSMS sets this by default, clients do not).

 

 

No answers yet. Maybe you can help?

Tags
General Discussions
Asked by
Sergiy
Top achievements
Rank 1
Share this question
or