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:
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)
