I have a ProfileAvatars
table with 1000 rows that is the parent table for UserProfile
table with 16,557,010 rows.
When I add a new picture (without any child record in UserProfile
) and I want to delete it, the Delete
statement takes 5-12 seconds to complete the delete query.
This is my query:
DELETE FROM ProfileAvatars
WHERE ProfileAvatarId = 10
Here is the execution plan:
Why is my delete
statement taking so long? How can I reduce time needed to delete a single row?
Tables definition
ProfileAvatars
:
CREATE TABLE [gnr].[ProfileAvatars](
[ProfileAvatarId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AvatarPictuerPath] [varchar](250) NOT NULL,
[Description] [nvarchar](250) NULL,
[CreateBy] [nvarchar](200) NOT NULL,
[RecordVersion] [timestamp] NOT NULL,
[CreatedDateUTC] [datetime] NOT NULL,
[UpdatedDateUTC] [datetime] NULL,
CONSTRAINT [PK_ProfileAvatars] PRIMARY KEY CLUSTERED
(
[ProfileAvatarId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
UserProfile
:
CREATE TABLE [sqr].[UserProfile](
[ProfileId] [bigint] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NOT NULL,
[ProfileCaption] [nvarchar](150) NULL,
[ProfileAvatarId] [int] NOT NULL,
[MediaAgeRangeId] [int] NOT NULL,
[IsKid] [bit] NOT NULL,
[IsDefault] [bit] NOT NULL,
[Active] [bit] NOT NULL,
[ApplicationId] [int] NOT NULL,
[CreateBy] [nvarchar](200) NOT NULL,
[RecordVersion] [timestamp] NOT NULL,
[CreatedDateUTC] [datetime] NOT NULL,
[UpdatedDateUTC] [datetime] NULL,
[DeletedDateUTC] [datetime] NULL,
[IsDeleted] [bit] NOT NULL,
[UserTasteInsertionDateUTC] [datetime] NULL,
[UserTasteStatus] [int] NULL,
[UserTasteSkipDateUTC] [datetime] NULL,
[PinCode] [varchar](20) NULL,
[IsLock] [bit] NOT NULL,
CONSTRAINT [PK_UserProfile] PRIMARY KEY CLUSTERED
(
[ProfileId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [sqr].[UserProfile] ADD CONSTRAINT [DF_UserProfile_IsDeleted] DEFAULT ((0)) FOR [IsDeleted]
GO
ALTER TABLE [sqr].[UserProfile] ADD CONSTRAINT [DF_UserProfile_IsLock] DEFAULT ((0)) FOR [IsLock]
GO
ALTER TABLE [sqr].[UserProfile] WITH CHECK ADD CONSTRAINT [FK_UserProfile_MediaAgeRange] FOREIGN KEY([MediaAgeRangeId])
REFERENCES [gnr].[MediaAgeRange] ([MediaAgeRangeId])
GO
ALTER TABLE [sqr].[UserProfile] CHECK CONSTRAINT [FK_UserProfile_MediaAgeRange]
GO
ALTER TABLE [sqr].[UserProfile] WITH CHECK ADD CONSTRAINT [FK_UserProfile_ProfileAvatars] FOREIGN KEY([ProfileAvatarId])
REFERENCES [gnr].[ProfileAvatars] ([ProfileAvatarId])
GO
ALTER TABLE [sqr].[UserProfile] CHECK CONSTRAINT [FK_UserProfile_ProfileAvatars]
GO
select @@version
of SQL Server