Timeout Expired error

Question

There is a Web Page in my Project in which RadGrid is there which displays multiple records in it. Sometimes the web page works fine but sometimes it shows below error-

In above snapshot, [Invoice].[ups_tbl_Request_Query_Select_AdminView] is the name of Stored Procedure.
Edit
Stored Procedure:

ALTER PROCEDURE [Invoice].[usp_tbl_Request_Query_Select_AdminView]
    -- Add the parameters for the stored procedure here

    @Status tinyint = Null,
    @SearchValue nvarchar(50) = Null

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

    If (LTRIM(@SearchValue) = '')
        Set @SearchValue = Null

    Select Distinct
        R.RequestID,
        R.RequisitionNo,
        R.BarcodeNo,
        R.IsThreeWayMatch,
        R.IsUrgent,
        R.CreatedBy,
        R.TotalAmount,
        R.TotalInvoiceAmount,
        R.CompanyCode as BuyerNo,
        W.Action,
        W.CreatedDate,
        --R.CreatedDate,
        R.SubmissionDate,
        CASE
        WHEN (R.IsThreeWayMatch in (1))
            THEN X.BuyerName   
        ELSE R.Company   
         END AS BuyerName,

        X.SupplierNo,
        X.SupplierName,
        X.InvoiceNo,
        S.RequestStatus,    
        --w.UserName,
        --(select top 1 w.CreatedDate from Invoice.tbl_WorkflowHistory W where w.CreatedBy = R.CreatedBy and R.RequestID = W.RequestID order by w.CreatedDate desc) as date1,
        (select top 1 w.UserName from Invoice.tbl_WorkflowHistory (NOLOCK) W where w.CreatedBy = R.CreatedBy and R.RequestID = W.RequestID order by w.CreatedDate desc) as UserName

    INTO #Records1

    From Invoice.tbl_Request (NOLOCK) R
        Left Join Invoice.tbl_Xml(NOLOCK) X On X.XmlID = R.XmlID
        Left Join Invoice.tbl_WorkflowHistory (NOLOCK) W On W.RequestID = R.RequestID
        Left Join Invoice.tbl_RequestStatus (NOLOCK) S On S.RequestStatusNo = R.[Status]
    Where ((IsNull(R.RequisitionNo, '') Like '%' + Coalesce(@SearchValue, R.RequisitionNo, '') + '%')
            Or (IsNull(R.BarcodeNo, '') Like '%' + Coalesce(@SearchValue, R.BarcodeNo, '') + '%')
            --Or (IsNull(X.BuyerNo, '') Like '%' + Coalesce(@SearchValue, X.BuyerNo, '') + '%')
            --Or (IsNull(X.SupplierNo, '') Like '%' + Coalesce(@SearchValue, X.SupplierNo, '') + '%')
            Or (IsNull(R.Company, '') Like '%' + Coalesce(@SearchValue, R.Company, '') + '%')
            Or (IsNull(X.SupplierName, '') Like '%' + Coalesce(@SearchValue, X.SupplierName, '') + '%'))
        --And (W.[Role] = 'AP Admin'
        --  Or W.[Role] = 'AP Associate')
        And R.[Status] = IsNull(@Status ,R.[Status]) 
    Order By R.RequisitionNo


    SELECT * FROM 
    (
    SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY RequisitionNo ORDER BY CreatedDate DESC) RowNumber FROM #Records1 ) AS t
    WHERE RowNumber = 1

    ) AS a Order By RequisitionNo

END

HTML code:

<asp:ObjectDataSource ID="odsDocumentHistoryAdmin" runat="server" SelectMethod="Query_Test" TypeName="atQuest.Projects.Sunway.IPRRequest" SortParameterName="orderBy">
                        <SelectParameters>
                            <asp:ControlParameter ControlID="ddlStatus" Name="status" PropertyName="SelectedValue"
                                Type="Int32" ConvertEmptyStringToNull="true"  />
                            <asp:ControlParameter ControlID="txtSearch" Name="searchValue" PropertyName="Text"
                                Type="String" ConvertEmptyStringToNull="true" />
                        </SelectParameters>
                    </asp:ObjectDataSource>
                    <telerik:RadGrid ID="qgDocumentHistoryAdmin" runat="server" DataSourceID="odsDocumentHistoryAdmin"
                                AllowPaging="True" AllowSorting="True" AllowFilteringByColumn="True" ShowGroupPanel="True"
                                CellSpacing="0" GridLines="None" Width="100%" Skin="Outlook">
                                <ClientSettings AllowDragToGroup="True" />
                                <GroupingSettings CaseSensitive="false"></GroupingSettings>
                                <MasterTableView AllowFilteringByColumn="true" AllowMultiColumnSorting="false" AutoGenerateColumns="false"
                                    CommandItemDisplay="Top" DataKeyNames="RequisitionNo" EnableGroupsExpandAll="true"
                                    GroupLoadMode="Client" PageSize="50">
                                    <CommandItemSettings ShowAddNewRecordButton="false" ShowExportToExcelButton="true" />
                                    <SortExpressions>
                                        <telerik:GridSortExpression FieldName="RequisitionNo" SortOrder="Descending" />
                                    </SortExpressions>
                                    <PagerStyle AlwaysVisible="True" PageSizeControlType="RadComboBox" Position="Bottom" PageSizes="50,100,150,200" />
                                    <Columns>
                                        //all GridBound columnc
                                    </Columns>
                                </MasterTableView>
                                <ExportSettings SuppressColumnDataFormatStrings="True" IgnorePaging="True" ExportOnlyData="True" Excel-Format="ExcelML" OpenInNewWindow="True" FileName="eAPDocHistory" Excel-FileExtension="xls">
                                </ExportSettings>
                            </telerik:RadGrid>

Class file code:

[DataObjectMethod(DataObjectMethodType.Select, true)]
        public static IPRRequests Query_Test(int? status, string searchValue, string orderBy)
        {
            IPRRequests iPRRequests = new IPRRequests();
            Data data = new Data();
            Dictionary<string, object> input = new Dictionary<string, object>()
            {
                {"@Status", status},
                {"@SearchValue", searchValue}
            };
            DataTable dt = data.ExecuteQuery(CommandType.StoredProcedure, "[Invoice].[usp_tbl_Request_Query_Select_AdminView]", input);
            if (dt != null && dt.Rows.Count > 0)
            {
                DataView dv = dt.DefaultView;
                if (!string.IsNullOrEmpty(orderBy))
                {
                    string colName = orderBy.Replace(" DESC", "");
                    try
                    {
                        //dv.Sort = orderBy.Replace(colName, FieldMapping[colName.Trim()]);
                        dv.Sort = orderBy;
                    }
                    catch (Exception)
                    {
                        throw new Exception("orderby:" + orderBy + ";colname:" + colName);
                    }

                    dt = dv.ToTable();
                }

                foreach (DataRow dr in dt.Rows)
                {
                    iPRRequests.Add(new IPRRequest(dr, true));
                }
                dt.Dispose();
                dv.Dispose();
            }

            return iPRRequests;
        }

I am unable to understand the reason of this error which occurs most of the time but not always.
Please let me know the reason of this issue and how can I resolve it in a simple way.
Please reply.

Answer 1

this is basically database not communication with the webpage. Either there is some connection string problem or it may be your net connection is slow, if your database is online

Answer 2

The view which you are loading have a lot of records and this leads to timeout, because there is not enough time for loading. The easiest solution for this is to increase the timeouts. But I don't advise this approach.

The smart way to do it is to load only the records which are visible. I'm giving you an example. When the page is open, you see for an example only 20 records. So your query should load only 20 records. You can check SQL RowNumber. In this article you are interest in Returning a subset of rows.

Here is an example

SELECT * FROM
    (SELECT ROW_NUMBER() 
        OVER (ORDER BY EmployeeName) AS Row, 
        EmployeeId, EmployeeName, Salary 
    FROM Employees) AS EMP
WHERE Row BETWEEN 20 AND 40

This is an easy example how to show only specific records. So if you have 20 records per page and you are on page 2, you will have query like this.

Answer 3

If you are using a SqlCommand object set it like this:

your command object.CommandTimeout = 0;

And see if that solves your problem. If it does you can leave it like that or change the value to something that you feel is large enough.

Answer 4

You should look at your query to see why it's taking so long. Sometimes your query is running longer than the default command timeout of 30 seconds.

The SqlCommand.CommandTimeout Property have a default command timeout of 30 seconds. You can try to increase it to 60 seconds.

The following code was taken from MSDN site.

 using (SqlConnection connection = new SqlConnection(connectionString))
 {
     connection.Open();
     SqlCommand command = new SqlCommand(queryString, connection);
     // Setting command timeout to 60 second
     command.CommandTimeout = 60;
     try {
         command.ExecuteNonQuery();
     }
     catch (SqlException e) {
         Console.WriteLine("Got expected SqlException due to command timeout ");
         Console.WriteLine(e);
     }
}