DELETE Command taking a lot of time with clustered index

179 Views Asked by At

I don't have large amount of data in my table. it contains only 16000 records. I want to delete around 33 records from this table which is taking 9-10 seconds. Below is the schema of the table and Query that I am trying for deletion. Please check and let me know the reason and solution for this.

I am sharing the execution plan image also for the reference:

enter image description here

Schema:

CREATE TABLE [dbo].[Report]
(
    [ReportId] [bigint] IDENTITY(1,1) NOT NULL,
    [WorkId] [int] NOT NULL,
    [GeneratedOn] [datetime2](7) NOT NULL,
    [Type] [nvarchar](450) NULL,
    [CGuid] [uniqueidentifier] NOT NULL,
    [CreatedBy] [nvarchar](max) NULL,
    [CreatedDate] [datetime2](7) NOT NULL,
    [EGuid] [uniqueidentifier] NOT NULL,
    [UpdatedBy] [nvarchar](max) NULL,
    [UpdatedDate] [datetime2](7) NOT NULL,
    [Document] [nvarchar](max) NULL,
    [SessionId] [uniqueidentifier] NOT NULL,

    CONSTRAINT [PK_Report] 
        PRIMARY KEY CLUSTERED ([ReportId] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Report] 
    ADD DEFAULT (GETUTCDATE()) FOR [CreatedDate]
GO

ALTER TABLE [dbo].[Report] 
    ADD DEFAULT (GETUTCDATE()) FOR [UpdatedDate]
GO

ALTER TABLE [dbo].[Report] 
    ADD DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [SessionId]
GO

ALTER TABLE [dbo].[Report] WITH CHECK 
    ADD CONSTRAINT [FK_Report_Work_WorkId] 
        FOREIGN KEY([WorkId]) REFERENCES [dbo].[Work] ([WorkId])
GO

ALTER TABLE [dbo].[Report] CHECK CONSTRAINT [FK_Report_Work_WorkId]
GO

Query:

-- This select query is executing faster and completed in 1 second
SELECT r.ReportId   
INTO #ReportIds
FROM dbo.Report r  
JOIN dbo.Work w ON r.WorkId = w.WorkId AND w.TimePeriodId = 2 
WHERE r.[Type] = 'BalanceReport'

-- This delete query is taking 9-10 seconds to delete 33 records.
DELETE r  
FROM dbo.Report r  
WHERE r.ReportId IN (SELECT ReportId FROM #ReportIds)
0

There are 0 best solutions below