CODEWITHSUNDEEP
sql-server
sajbeer
sajbeer

Reputation: 201

The wait operation timed out

I have 2 tables,each contains 4-500k records

CREATE TABLE [dbo].[User][UserId] [int] IDENTITY(1,1) NOT NULL,
[Password] [nvarchar](max) NULL,
[RoleId] [int] NOT NULL,
[Name] [nvarchar](max) NULL,
[Address] [nvarchar](max) NULL,
[Email] [nvarchar](max) NULL,
[Landline] [nvarchar](max) NULL,
[MobileNumberCode] [int] NULL,
[MobileNumber] [nvarchar](max) NULL,
[DateOfBirth] [datetime] NULL,
[MarriageDate] [datetime] NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
[Status] [nvarchar](max) NOT NULL,
[BranchId] [int] NULL,
[UserTitle] [nvarchar](50) NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[HouseNumber] [nvarchar](50) NULL,
[BuildingNumber] [nvarchar](50) NULL,
[RoadNumber] [nvarchar](50) NULL,
[BlockNumber] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[NearBranchId] [int] NULL,
[MobileIsValid] [bit] NULL,
[EmailIsValid] [bit] NULL,
[Gender] [nvarchar](50) NULL,
[SourceId] [int] NULL)

CREATE TABLE [dbo].[PurchaseOrder]
[PurchaseOrderId] [int] NOT NULL,
[BranchId] [int] NOT NULL,
[PurchaseDate] [datetime] NOT NULL,
[Amount] [decimal](18, 3) NOT NULL,
[UserId] [int] NOT NULL,
[Status] [nvarchar](max) NULL,
[sbs_no] [int] NOT NULL)

And I have stored procedure to get data from these tables using join.

CREATE PROC Sp_SearchCustomer (@FromDate datetime = null,
@ToDate datetime = null,
@RegFromDate datetime = null,
@RegToDate datetime = null)
AS
BEGIN
  select a.UserId,a.Name,b.PurchaseOrderId,b.Amount from dbo.[User] a left      join PurchaseOrder b on a.UserId=b.UserId
where 
((a.CreatedDate >= ''' + cast(@RegFromDate as varchar) + ''')
AND (a.CreatedDate <= ''' + cast(@RegToDate as varchar) + ''')) 
and ((b.PurchaseDate >= ''' + cast(@FromDate as varchar) + ''')
AND (b.PurchaseDate <= ''' + cast(@ToDate as varchar) + ''')) 
END

When executing this procedure with date, its getting "The wait operation timed out" exception. Please help to solve this issue.

Upvotes: 1

Views: 335

Answers (1)

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

Your date in your tables and in your Procedure are both saved as varchar. This is perfect and there is no need to convert them to varchar.

Beside, varchar is surrounded by quotes and won't be executed. This is just becoming a string: 

 where ((a.CreatedDate >= 'cast(@RegFromDate as varchar)')...

There are also way too many useless parenthesis since you are using AND.

Try this instead:

CREATE PROC Sp_SearchCustomer (
    @FromDate datetime = null,
    @ToDate datetime = null,
    @RegFromDate datetime = null,
    @RegToDate datetime = null
)
AS
BEGIN
    SELECT a.UserId
        ,a.Name
        ,b.PurchaseOrderId
        ,b.Amount 
    FROM dbo.[User] a 
    LEFT JOIN PurchaseOrder b 
        ON a.UserId = b.UserId
    WHERE 
        a.CreatedDate >= @RegFromDate 
        AND a.CreatedDate <= @RegToDate 
        AND b.PurchaseDate >= @FromDate 
        AND b.PurchaseDate <= @ToDate 
END

Once the query has been improved, you can test it again. You should also look at Statistics and Indexes and make sure that Statistics are up-to-date and Indexes are not fragmented.

For Statistics, you can use: exec sp_updatestats For Indexes on these 2 tables, look at the Fragmentation % and choose to REBUILD or REORGANIZE them.

Upvotes: 2

Related Questions