The wait operation timed out SOURCE...............: FORM.................: http://localhost/Pages/Reports/TDSDetails.aspx

Question

Date.................: 6/26/2025

Time.................: 6:57:03 PM

MESSAGE..............: The wait operation timed out

SOURCE...............:

FORM.................: http://localhost/Pages/Reports/TDSDetails.aspx

QUERYSTRING..........:

TARGETSITE...........:

STACKTRACE...........: <br/>

^^-------------------------------------------------------------------^^

USE [PowerERM_V5.6_Prod]

GO

/****** Object: StoredProcedure [dbo].[SP_Get_TDSDetails] Script Date: 6/27/2025 10:23:24 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*----------------------------------------------------------

Created By : Suhail

Date : 07-Sep-2012

Desc : Fetches TDS Details

exec SP_Get_TDSDetails 1, '', 2, 2014, 4, 2015, 3, 'en-US'

----------------------------------------------------------*/

ALTER PROCEDURE [dbo].[SP_Get_TDSDetails]

@locationId NVARCHAR(10)

,@departmentId NVARCHAR(10)

,@employeeId NVARCHAR(10)

,@fromYear NVARCHAR(4)

,@fromMonth NVARCHAR(2)

,@toYear NVARCHAR(4)

,@toMonth NVARCHAR(2)

,@culture NVARCHAR(32)

-- WITH ENCRYPTION

AS

BEGIN



    DECLARE @typeId INT

	      

    SELECT  @typeId = TypeId

    FROM    tCultureMaster

    WHERE   CultureCode = @culture

    

    DECLARE @dateFormat INT

    IF EXISTS ( SELECT  1

                FROM    dbo.tCultureMaster

                WHERE   CultureCode = @culture

                        AND TypeId = 1 ) 

        SET @dateFormat = 101 ;

    ELSE 

        IF EXISTS ( SELECT  1

                    FROM    dbo.tCultureMaster

                    WHERE   CultureCode = @culture

                            AND TypeId = 2 ) 

            SET @dateFormat = 103 ;

    DECLARE @iFromYear INT = CAST(@fromYear AS INT)

    DECLARE @iToYear INT = CAST(@toYear AS INT)

    DECLARE @iFromMonth INT = CAST(@fromMonth AS INT)

    DECLARE @iToMonth INT = CAST(@toMonth AS INT)



	declare @fromDate datetime, @toDate datetime

	set @fromDate = cast(cast (@fromMonth as varchar) + '/01/'+ cast(@fromYear as varchar) as datetime)

	set @toDate = cast(cast (@toMonth as varchar) + '/01/'+ cast(@toYear as varchar) as datetime)

	DECLARE @t TABLE ( EmployeeID INT )

    IF ( LEN(@employeeId) > 0 ) 

        BEGIN

            INSERT  INTO @t

                    SELECT  value

                    FROM    dbo.fn_Split(@employeeId, ',')

        END

    ELSE 

        BEGIN

        ------------------------TRANSFER FIX--------------------------

            INSERT  INTO @t

                    SELECT DISTINCT

						a.EmployeeID

                    FROM    dbo.tEmployeePayrollProcessingAttendanceInfo a

					INNER JOIN v_Employees v ON a.EmployeeId = v.EmployeeID

						AND (cast(cast(a.month as varchar) +'/01/'+ cast(a.Year as varchar) as datetime)

								between @fromDate and @toDate)

						-- AND a.Year IN (

						-- @fromYear, @toYear )

						-- AND a.month BETWEEN @fromMonth AND @toMonth

						AND a.LocationId = @locationId

						AND v.DepartmentId = ( CASE

							WHEN @departmentId > 0

							THEN @departmentId

							ELSE v.DepartmentId

							END )

		------------------------TRANSFER FIX--------------------------

        END

    SELECT  earnings.EmployeeID

           ,earnings.EmployeeName

           ,CONVERT(NVARCHAR, earnings.DateOfJoining, @dateFormat) AS Dateofjoining

           ,earnings.LocationName

           ,earnings.PFAccountNo

           ,earnings.SalaryBankAccountNo

           ,( SELECT    DATENAME(MONTH,

                                 DATEADD(MONTH, earnings.[month], 0) - 1)

            ) AS [Month]

           ,earnings.[year] AS [Year]

           ,earnings.Department

           ,earnings.Designation

           ,earnings.DaysPaid

           ,earnings.EarningName

           ,earnings.EarningValue

           ,deductions.DeductionName

           ,deductions.DeductionValue

           ,earnings.SalarySequence

    FROM    ( SELECT    ss.EmployeeID

                ,ss.EmployeeName AS EmployeeName

                ,ss.DateOfJoining AS DateOfJoining

                ,ss.Location AS LocationName

                ,ss.PFAccountNo

                ,ss.BankAccountNo AS SalaryBankAccountNo

                ,ss.month AS [month]

                ,ss.year AS [year]

                ,CASE @typeId

                    WHEN 1 THEN ss.Department /*English*/

                    ELSE ss.DepartmentArabic /*Arabic*/

                END AS Department   --Column Name

                ,CASE @typeId

                    WHEN 1 THEN ss.Designation /*English*/

                    ELSE ss.DesignationArabic /*Arabic*/

                END AS Designation   --Column Name

                ,ss.DaysPaid

                ,ss.SalaryItemID

                -- Modify by waseem 28-09-2018 if not work remove comment

                --,ROW_NUMBER() OVER ( PARTITION BY ss.SalaryItemType,

                --                    ss.SalarySequence,

                --                    ss.EmployeeID ORDER BY ss.SalaryItemID DESC ) AS seq

                -- remove below two lines

                

                ,ROW_NUMBER() OVER (PARTITION BY ss.month

										 ORDER BY ss.EmployeeID DESC ) AS seq

                ,ss.ItemTypeDesc AS EarningName

                ,ss.Value AS EarningValue

                ,ss.SalaryItemType

                ,ss.SalarySequence

              FROM      tEmployeeSalarySlip ss

				LEFT JOIN dbo.v_Employees ve ON ve.EmployeeID = ss.ApprovedBy

				LEFT JOIN dbo.tModeOfPayment mop ON mop.ModeofPaymentID = ss.ModeOfPaymentID

              WHERE     SalaryItemType = 1

				AND ss.IsApproved = 1

				AND ss.IsOnHold = 0

				AND (cast(cast(ss.month as varchar) +'/01/'+ cast(ss.Year as varchar) as datetime)

						between @fromDate and @toDate)

				-- AND ss.year IN ( @iFromYear, @iToYear )

				-- AND ss.month BETWEEN @iFromMonth AND @iToMonth

				AND ss.LocationId = @locationId

				AND ss.EmployeeID IN ( SELECT   EmployeeID FROM     @t )

            ) earnings

            LEFT OUTER JOIN 

			( 

				SELECT    ss.EmployeeID

					,ss.month AS [month]

					,ss.year AS [year]

					,ss.SalaryItemID

					  -- Modify by waseem 28-09-2018 if not work remove comment

					--,ROW_NUMBER() OVER (PARTITION BY ss.SalaryItemType, 

					--					ss.SalarySequence,

					--					ss.EmployeeID ORDER BY ss.SalaryItemID DESC ) AS seq

					

					-- Remove below two lines

					,ROW_NUMBER() OVER (PARTITION BY ss.month

										 ORDER BY ss.EmployeeID DESC ) AS seq

					,ss.ItemTypeDesc AS DeductionName

					,ss.Value AS DeductionValue

					,ss.SalaryItemType

					,ss.SalarySequence

					FROM      tEmployeeSalarySlip ss

					WHERE     SalaryItemType = 2

					AND ss.IsApproved = 1

					AND (cast(cast(ss.month as varchar) +'/01/'+ cast(ss.Year as varchar) as datetime)

							between @fromDate and @toDate)

					-- AND ss.year IN ( @iFromYear, @iToYear )

					-- AND ss.month BETWEEN @iFromMonth AND @iToMonth

					AND ss.LocationId = @locationId

					AND ss.EmployeeID IN (SELECT EmployeeID FROM @t)

				) deductions ON earnings.EmployeeID = deductions.EmployeeID

						AND earnings.seq = deductions.seq

						AND earnings.SalarySequence = deductions.SalarySequence

						AND earnings.[month] = deductions.[month]

						AND earnings.[year] = deductions.[year]

    WHERE (cast(cast(earnings.month as varchar) +'/01/'+ cast(earnings.Year as varchar) as datetime) between @fromDate and @toDate)

			-- earnings.year IN ( @iFromYear, @iToYear )

            -- AND earnings.month BETWEEN @iFromMonth AND @iToMonth

            AND earnings.EmployeeID IN ( SELECT EmployeeID FROM   @t )

    ORDER BY earnings.[month]

    ,earnings.SalarySequence

           ,earnings.SalaryItemID

END  

<Setting Key ="CommandTimeOut" Value ="600"></Setting>

i am using this above code to load my 1 year data but it take too much time and not give me the data also manually when i exec SP_GET_TDSDetails it show me the data of 1 year but when i run the code it shows loading then no data has been load i debug and get above error in catch

Answer 1

Thank you for reaching out.

When loading one year of data through the ASP.NET page (TDSDetails.aspx), the process hangs and eventually throws “The wait operation timed out”. However, running EXEC SP_Get_TDSDetails manually in SQL Server returns results quickly.

The stored procedure works fine in SSMS because it runs without the web application’s command timeout limit. In your code, the default SqlCommand.CommandTimeout is 30 seconds. Even though you set <Setting Key="CommandTimeOut" Value="600">, this might not be applied correctly or the query is still too heavy for the web request pipeline.

Possible reasons for slowness:

• The procedure processes a large dataset (1 year) with multiple joins and row-number operations.
• Missing indexes on columns used in JOIN, WHERE, and BETWEEN conditions (e.g., month, year, LocationId, EmployeeID).
• The web layer may not be using the configured timeout setting.

Steps to resolve:

1. Verify CommandTimeout in code:\ Ensure you explicitly set it in your data access code:

      SqlCommand cmd = new SqlCommand("SP_Get_TDSDetails", conn);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.CommandTimeout = 600; // 10 minutes
   

   The <Setting> in config alone may not apply unless your DAL reads it. Optimize the stored procedure:
   • Add proper indexes on tEmployeeSalarySlip and tEmployeePayrollProcessingAttendanceInfo for LocationId, EmployeeID, month, year.
     • Avoid casting in WHERE clauses; use proper date columns or computed columns.
       • Check if ROW_NUMBER() logic can be simplified or moved to client-side.
       Test with smaller ranges:\ Confirm if the issue is only with large ranges. If smaller ranges work, consider paging or splitting the request into chunks. Consider async or background processing:\ For large reports, generate data asynchronously and export to file instead of loading in a single web request.

Reference:\

SqlCommand.CommandTimeout Property

Performance monitoring and tuning tools

Please let us know if you require any further assistance, we’re happy to help.

If you found this information useful, kindly mark this as "Accept Answer".